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)
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
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)
Code:
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