Create New link to External Database table

[N.Ordiers]

Is there a way to programmatically create a link to an external database
containing multiple tables?
I am working under Access 2003 and distribute an *.mde file to 6 different
location with out any tables linked, all the locations use the same tables
names but with individual data, when an update is made to the program I link
the tables manually.

Any help in the subject, will be greatly appreciated.

 

[Leo]

ADO is your best bet. You keep the connection open only as long as is
necessary to update or get data. I always link my tables (So I can simplify
my Connection string to CurrentProject.Connection), but keep my forms unbound
for speed issues.

Here is the Cliff Notes version of using ADO.

***************************
*** To fill a form w/ pre-existing data

Dim rst as ADODB.Recordset
Set rst = new ADODB.Recordset
Dim cn as Stringcn = "Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Perisist Security Info = False; " & _
"User ID = Admin; " & _
"DataSource = EmployeeServer\EmployeeDatabase.mdb"

With rst
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from EmployeeTable; "
End With

Me.ID = rst!ID
Me.FirstName = rst!FirstName
Me.LastName = rst!LastName
Me.Manager = rst!Manager

rst.Close
Set rst = Nothing


******************************
*** To add a record

Dim rst as ADODB.Recordset
Set rst = new ADODB.Recordset
Dim cn as String

cn = "Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Perisist Security Info = False; " & _
"User ID = Admin; " & _
"DataSource = EmployeeServer\EmployeeDatabase.mdb"

With rst
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from EmployeeTable; "'
.AddNew
!FirstName = Me.FirstName
!LastName = Me.LastName
!Manager = Me.Manager
.Update

End With
me.ID = rst!ID

rst.Close
Set rst = Nothing

******************************************
*** To edit an existing record is a little trickier because you
*** have to use a "Find" clause.

Dim rst as ADODB.Recordset
Set rst = new ADODB.Recordset
Dim cn as String
Dim strFind as String

cn = "Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Perisist Security Info = False; " & _
"User ID = Admin; " & _
"DataSource = EmployeeServer\EmployeeDatabase.mdb"
strFind = "ID = '" & CStr(Me.ID) & "'"
With rst
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from EmployeeTable; "'
.Find strFind
!FirstName = Me.FirstName
!LastName = Me.LastName
!Manager = Me.Manager
.Update

 

[Marshall Barton]

Is there a way to programmatically create a link to an external database
containing multiple tables?
I am working under Access 2003 and distribute an *.mde file to 6 different
location with out any tables linked, all the locations use the same tables
names but with individual data, when an update is made to the program I link
the tables manually.

The linking information is in the table defs' Connect
property. For a Jet database, it is just:
";DATABASE=pathtobackend.mdb"

Omce you figure out where the table's database is, just set
the property:

DbEngine(0)(0).TableDefs!nameoftable.Connect = _
";DATABASE=pathtobackend.mdb"

 

[N.Ordiers]

Thanks Leo will give it a try, sorry for the delay.

 

[N.Ordiers]

Thanks, I will also try your way. I was out town and could not respond any
sooner

 

[N.Ordiers]

The problems that I am comfronted is there are two external databases
containing various tables. All this tables need to be link to another
database that does not have any tables. I am currently doing it manually but
there must be a way to programatically create a new link to an external table