Create New link to External Database table

  • Thread starter Thread starter N.Ordiers
  • Start date Start date
N

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.
 
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
 
N.Ordiers said:
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"
 
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
 
Back
Top