Writing data from Excel 2010 to Access 2010 password protected database

[AlexCS]

Hi everyone,

I am trying to transfer Excel data to a password protected Access 2010 database. So far I have the following code that works perfectly if the database is unprotected (minus the JET OLEDB Password part)

Dim MyConnXLS As String
Dim MyConnADODB As String
Dim MyConn As New ADODB.Connection
Dim MyRecordset As ADODB.Recordset
Dim MyTable As ADODB.Recordset
Dim MySQL As String

myworbook = ThisWorkbook.FullName
ThisWorkbook.Activate
MySheet = ActiveWorkbook.Sheets(6).Name

MySQL = "SELECT * FROM [" & MySheet & "$A:M]" & " WHERE Number <> 0 "
MyConnXLS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myworbook & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;Readonly=True"""

Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MySQL, MyConnXLS, adOpenStatic, adLockReadOnly
MyConnADODB = "Provider = Microsoft.ACE.OLEDB.12.0;" & "Data Source =" & MyPath & "\FreightCosts.accdb" '& "Jet OLEDB:Database Password" = "Pass123"
Set MyTable = New ADODB.Recordset
MyTable.Open "ExtraCosts", MyConnADODB, adOpenDynamic, adLockOptimistic

Do Until MyRecordset.EOF
MyTable.AddNew
MyTable!OrderNumber = MyRecordset!OrderNumber
MyTable.Update
MyRecordset.MoveNext
Loop

This code gets an error when trying to open the table, namely line MyTable.Open "ExtraCosts", MyConnADODB, adOpenDynamic, adLockOptimistic.
Does anyone have any idea what can be done to work around this problem? The error is Data source name not found and no default driver specified.

Any suggestions are very much appreciated!

Alex

 

[Kyle123]

The syntax for your connection string is all out of whack - there are missing semi colons and iffy apostrophes:

MyConnADODB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyPath & "\FreightCosts.accdb";Jet OLEDB:Database Password=Pass123;"

As an aside, you shouldn't query the open workbook with ADO, it causes memory leaks. As far as I can see from your code above, it would be just as easy to loop through the range and add what ever you need

 

[AlexCS]

Thanks Kyle, I was beginning to think my question was very difficult . Unfortunately I tried all possible combinations and I usually get "Not a Valid Password" as an error message.
If I am not mistaken, I think the line you suggested was missing an apostrophe as well or had one too many. I ended up using the below lines but I will keep looking, there is surely a combination that I missed.

[COLOR=#333333]MyConnADODB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyPath & "\FreightCosts.accdb" & ";Jet OLEDB:Database Password=Pass123;"[/COLOR]

or

[COLOR=#333333]MyConnADODB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyPath & "\FreightCosts.accdb;Jet OLEDB:Database Password=Pass123;"
[/COLOR]

Cheers!
Alex

 

[Kyle123]

Both of those are identical except the trailing semi-colon