X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Import Excel Data, Access 2007    
post Dec 17 2013, 03:42 PM

Posts: 9
Joined: 9-September 09

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:
im cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=PAERSCBBxxx;" & _
"Initial Catalog=mydatabasename;UID=;PWD=;"
strSQL = "SELECT * INTO dbo.CC_TIME_xxxxxxx 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.
Go to the top of the page
post Dec 17 2013, 09:58 PM

UA Admin
Posts: 36,194
Joined: 20-June 02
From: Newcastle, WA

Your connection string specifies ZLSs for the UID and PWD. Is that correct? Does your SQL Server database truly have blank userID and password?
If you are trying to use Windows Authentication, not SQL Server Authentication, the syntax is different. Look here for a wealth of connection string information.
Go to the top of the page
post Oct 10 2016, 02:19 AM

UtterAccess VIP
Posts: 4,053
Joined: 19-October 10

Please be aware that SQL on Excel will frequently cause data loss.
For example based on formatting you can lose time information from dates, decimal information from numeric information and all information in fields where Excel guesses the wrong data type and for example defines a text column as numeric.

You have to have really good data that is super consistent, all formatted perfectly and with few empty fields to be sure your data will be ok using SQL in Excel.
Go to the top of the page
post Oct 10 2016, 03:51 AM

Posts: 7,115
Joined: 22-December 10
From: England

Not being familiar with your set up, but can you not use DAO, and just set up the link using the linked table manager and saving the password?
I'd also echo JonSmith's comment about the interesting nature of imports from Excel (and with all those caveats)

The last time I did such an import I had to first open the spreadsheet and set all the column formats to the way I wanted them -which was mostly forcing the 'numbers' to text then save and close it first.
In that case the actual data was known to be good, so I created a linked table: But even then had to set some of my Access tables to be text size 255.

BTW the linked table approach was the only practical way I found to import from Excel if there were more than 65535 rows.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    12th December 2019 - 07:07 AM