SQL7 and Access with ODBC

[Mike Leigh]

Hi,

I am slightly stuck here...

I have a DB which I install onto a client workstation. As the db only includes linked tables to a SQL Server I don't use a frontend / backend access database solution. What I am having problems with is the linked table manager. I do not want to use it. I would like to have some code to check the tables and update them when the db is opened. I would like to be able to store the path and server details etc... in a table and get the code to pull them from the table. That way when I add new forms and functionality it will automtaicaly do this for me. I also do not want to use the ODBC drivers on the workstations because that means I need to set them up. Has anyone come across this before ??? Because I am really pulling my hair out when I email the frontend to a client and then each workstation needs reconfiguring. :-(

Please post anything here that is of help

Thanks in advance

Mike Leigh

 

[Pat Hartman]

This article from the knowledge base might point you in the right direction:

Q210295 - ACC2000 How to Create a Customized Attach ODBC Table Form

 

[Mike Leigh]

Thanks Pat... But

This works great for selecting a table and creating a link. Thanks.

However I would like to do more than this.

What I would like to do is have a table in my db. e.g. tblAttachTables which stores the following :- UID,PWD,DATABASE,TABLENAME

Then have some code that when I click a button the code will get the first row and attach or refresh the table and go down until it reaches the end.

Example of tblAttachTables
sa,,db1,tableemployee
sa,,db1,tablehistory

I have missed out the DSN in the above example beacuse I will need to create a DSN ess connection and then include this in the code.

Please let me know if you get any ideas ?

Thanks in advance

Mike Leigh

 

[Pat Hartman]

I believe that the article I pointed you to contains all the elements you need to code your function. Instead of SELECT name FROM sysobjects, select the table list from your own table. Instead of populating a form and doing the connection one at a time, do them by looping through the query's recordset. Instead of hard coding the ODBC connect string, get it from your own table.