GilesForrest
IS-IT--Management
I'm having difficulty finding out how to do a (I think) simple task!
I have tables in a database secured with one workgroup file that I want to use in an unsecured database. The ideal way would be to use the ODBC driver for Access. I've set it up for the database and it's system database and it works fine if I use it in a database query in Excel, for example. I simply used the DSN and supplied the login details.
However, if I try to link it to another Access database, I get the following error message:
"You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database"
Hmmmm....
I found I can access the table in code:
Dim strDestWorkgroupFile As String
Dim strDestUserName As String
Dim strDestPassword As String
Dim strDestDatabase As String
strDestWorkgroupFile = "F:\Data\My Databases\Minimum \Secured.mdw"
strDestUserName = "forrestg"
strDestPassword = "test"
strDestDatabase = "F:\Data\My Databases\Database.mdb"
Dim rst As ADODB.Recordset
Dim cnn As New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Jet OLEDB:System database"
= strDestWorkgroupFile
cnn.Open "Data Source=" & strDestDatabase & ";User Id=" & strDestUserName & ";Password=" & strDestPassword & ";"
Set rst = New ADODB.Recordset
rst.Open "Table1", cnn, adOpenStatic, adLockOptimistic
rst.MoveFirst
Do Until rst.EOF
MsgBox rst.Fields("test"
rst.MoveNext
Loop
But that's not the ideal solution, because the data will be volumous and dynamic and we want to use it like a normal table so we can do SQL select queries, etc, etc.
Am I doing something wrong, or is there a way to produce the link in ADOX for example??
Thanks in advance for your help.
Giles
I have tables in a database secured with one workgroup file that I want to use in an unsecured database. The ideal way would be to use the ODBC driver for Access. I've set it up for the database and it's system database and it works fine if I use it in a database query in Excel, for example. I simply used the DSN and supplied the login details.
However, if I try to link it to another Access database, I get the following error message:
"You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database"
Hmmmm....
I found I can access the table in code:
Dim strDestWorkgroupFile As String
Dim strDestUserName As String
Dim strDestPassword As String
Dim strDestDatabase As String
strDestWorkgroupFile = "F:\Data\My Databases\Minimum \Secured.mdw"
strDestUserName = "forrestg"
strDestPassword = "test"
strDestDatabase = "F:\Data\My Databases\Database.mdb"
Dim rst As ADODB.Recordset
Dim cnn As New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Jet OLEDB:System database"
= strDestWorkgroupFilecnn.Open "Data Source=" & strDestDatabase & ";User Id=" & strDestUserName & ";Password=" & strDestPassword & ";"
Set rst = New ADODB.Recordset
rst.Open "Table1", cnn, adOpenStatic, adLockOptimistic
rst.MoveFirst
Do Until rst.EOF
MsgBox rst.Fields("test"
rst.MoveNext
Loop
But that's not the ideal solution, because the data will be volumous and dynamic and we want to use it like a normal table so we can do SQL select queries, etc, etc.
Am I doing something wrong, or is there a way to produce the link in ADOX for example??
Thanks in advance for your help.
Giles
