Importing Excel data to SQL table using MSAccess .adp

[batteam]

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.

 

[lameid]

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.

 

[batteam]

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.

 

[lameid]

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.