connection string for connecting to another Access DB?

[kelly d via AccessMonster.com]

In reading, I came across a reply to a post from a Giorgio Rancati for making
a connection to a sql DB be used for a forms record source. in this reply
Giorgio says:

Dim strSql AS String

strSql="Select * From " & _
"[ODBC;DSN=ClientSql;UID=Test;PWD=Test;DATABASE=Sample;].[TableName]"

subfrm.Form.RecordSource = strSql

I tried it. it works great for connecting to an sql DB and making it the
recordsource for a form.

In experimenting, I wanted to see if I could do the same thing with an Access
DB on my machine.
now, I know I can create and save an odbc connection, I can create a query to
connect, or programmatically, I can use OpenDatabase. all work. all have
their uses. but for knowledge purposes (and a couple other reasons I could
explain if anybody cared) I wanted to create a connection string to connect
an .MDB and programmatically assign it to a forms recordsource like the
sample code listed above.

I thought maybe I could just make it simple and grab a access connection
string from connectionstrings.com like:

"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.
mdb;Uid=Admin;Pwd=;"

and put that inside the brackets but alls I get is either, "Disk or Network
Error" or " you cant use ODBC to import from, export to, or link to an
external MS jet or ISAM DB table" depending on what keywords I tweak.
I cant seem to find the answer so I'm just asking,
Does anybody know what I need to type to make this work. (this, of course
being, me.recordsource="select * from [(fill in the blank)].[(table name];"

thanks.

 

[Uros]

I think the link table is the best choice. When table is linked then you
work with table like table is in current db.

Part of code for help:
------------------------
Dim myTable As TableDef, db0 As Database, DatabasePath as string

Set db0 = CurrentDb 'OR Set db0 =
DBEngine.Workspaces(0).Databases(0)
Set myTable = db0.CreateTableDef("linkTableName")
myTable.SourceTableName = "linkTableName"
DatabasePath = ApplicationPath & "\YourDBWithLinkTables.mdb"
StrConnectionString = ";DATABASE=" & DatabasePath
myTable.Connect = StrConnectionString
db0.TableDefs.Append myTable
..
..
..

 

[peregenem]

In experimenting, I wanted to see if I could do the same thing with an Access
DB on my machine.

SELECT *
FROM [MS Access;
DATABASE=C:\Test_jet_4_not_secured.mdb;].Test

SELECT *
FROM [MS Access;
DATABASE=C:\Test_jet_4_file_password.mdb;
PWD=passwordhere;].Test

SELECT *
FROM [MS Access;
Database=C:\Test_jet4_workgroup_security.mdb;
UID=MyID;PWD=MyPassword;].MyTable

Note for workgroup security it is not possible to specify the .mdw
workgroup security file, the target db must use the same .mdw file.

 

[kelly d via AccessMonster.com]

Thank you both for your replies.

Uros' reply would have been a good piece of code had I been looking to
programmatically add a link table to my table collection that was linked to
the external access DB.

admittedly, adding a linked table is probably the best approach, but for what
I was after, which was to try and get educated on how, technically, this sort
of thing could be done in code, Peregenem gave me exactly what I was after.
Thank you. based on 1 of half a dozen projects on my plate, I get the feeling
I'm going to need to know how to do this in the near future.

Again thank you both.