×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Importing Excel data to SQL table using MSAccess .adp

Importing Excel data to SQL table using MSAccess .adp

Importing Excel data to SQL table using MSAccess .adp

(OP)
I have an MSAccess project with linked SQL tables. I need to import data from an Excel spreadsheet into a SQL table that is linked to my Access project. I have the following code:

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=PAERSCBBxxxxx;" & _
"Initial Catalog=mydatabasename;UID=;PWD=;"

strSQL = "SELECT * INTO dbo.CC_table_name FROM " & _
"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=Y:\TECHNOLOGY\xxxxx.xlsx;" & _
"Extended Properties=Excel 8.0')" '''''''...[Customers$]"
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

When I attempt to run this code, I get the error that the Login failed for my user. But I am using Windows Authentication and thus understand that I don't have to specify a UID. Any assistance on this or if there is another code snippet to import the Excel data, would be appreciated. Thanks.


RE: Importing Excel data to SQL table using MSAccess .adp

I saw PHV recommending you post here...

Since all queries in a Project are SQL server based, you effectively have to accomplish it with SQL Server or possibly use ADO to open a SQL connection and an Excel connection with separate objects... You might play around with disconnected recordsets but I don't know about reconnecting to a separate system...

For SQL server, this is a bit beyond me but you could check out the SQL Server forums... Although I must say that using a mapped drive likely will fail with SQL. Better to go with UNC paths.

Honestly though, I'd chuck the ADP, put your frontend in Access Native format. Then you can do things like link to the data and SQL data... then it is a simple insert query. Even MS recommends not using ADP's. Or even as a one off make a native Access file to handle the import and append to SQL.

RE: Importing Excel data to SQL table using MSAccess .adp

(OP)
Thanks for the advice lameid.

What I ended up doing is creating a local table in my project, using TransferSpreadsheet command to get the data from Excel into that table then did a local 'Insert Into' stored procedure to get the data into my SQL table. For now, we have a SQL database/server set up that does allow me to 'save' stored procedures in the project. But when this goes into a 'production' server, I will not be able create objects. This time, it worked but like you said, next time I will simply go behind a form, open a connection to SQL, read in my data row by row from my local table and insert it with ADO into my SQL table. No big deal, only need to transfer a few hundred rows and only have to do this once a year. I just thought there must be a way to read directly from Excel into SQL.

Thanks again for your response.

RE: Importing Excel data to SQL table using MSAccess .adp

There is a native SQL way but I don't think it can be accomplished with an ADP... And then you have to be running SQL tools...

Are you saying there is a way to put the data into a local table in an ADP?

I guess another way to go is to use transferspreadsheet to load the data directly to a SQL table in your ADP but that requires knowing the layout absolutely upfront. Then you could have a Sproc to delete all the data before loading or in this case I would just truncate it instead.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close