Prevent duplicate table connection (1 Viewer)

siculster · Sep 8, 2009

Hi

Apologies in advance...my VBA/module knowledge is at an early stage!

I have successfully linked two SQL tables with a AutoExec macro, using the following vba code:

Code:

Public Function SQLlink()

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver=SQLServer};Server=servername;Database=databasename; Uid=username;Pwd=password", acTable, "sourcetable1", "destinationtable1"
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQLServer};Server=servername;Database=databasename; Uid=username;Pwd=password", acTable, "sourcetable2", "destinationtable2"

End Function

Unfortunately, this creates a duplicate of both table links everytime I load up the database. How do I go about ensuring that the link is only created once, and that the linked tables are removed on exit (despite multiple users logging in)?

Many thanks

pbaldy · Sep 8, 2009

Not sure why you bother linking the tables every time, but you can use the DeleteObject method to delete the linked tables when the application closes (probably in the unload event of some form that would be open until the application closes).

If multiple users are using the application, it should be split so that each user has their own copy of the front end:

http://allenbrowne.com/ser-01.html

Atomic Shrimp · Sep 8, 2009

Why would you want to create the link on open and remove it on close?

Just closing an access DB leaving the link intact will achieve the same effect as deleting the link - it will release any connection to the back end table.

Prevent duplicate table connection (1 Viewer)

siculster

Registered User.

pbaldy

Wino Moderator

Atomic Shrimp

Humanoid lifeform

Similar threads

Users who are viewing this thread