ODBC Link from Access 2000 & XP to SQL 2000 problems

[Paul]

Hello,

I am converting an Access database on our network to a sql 2000
backend and keeping access as the front end. The access database has
evolved and been a solution to collect data but now we want to move
the tables over to sql 2000. We have successfully done that but are
now having difficulties with an ODBC connection. Initiall linking the
tables is not an issue and I am able to connect with no problem.
However after closing down the ODBC database and then going back in
the ODBC connection doesnt get refreshed and errors out when you try
to open the table. It then asks to redo connection. Because we have
over 40 people in this file, we want to make the ODBC connection
authenticate perhaps in the autoexec macro or the main form Load event
so people dont keep getting this error.

Does anyone know if there is a way to refresh the ODBC connection each
time you logon prompting for user name and password on the main form
load event or something. To have people go into the refresh linked
tables ect...would be way over their head.

Thanks,
Paul

[TJ]

You need to delete all of the current ODBC tables in the database and
relink the SQL tables. You should also check to make sure each user
has the same SQL ODBC driver, and ODBC DSN name. If the name doesn't
match, then you will have issues.

We had a SQL database with 40 tables and a few tables had >2 million
records. What you are wanting to do is definitely feasible.
TJ

Paul wrote:

Hello,

I am converting an Access database on our network to a sql 2000
backend and keeping access as the front end. The access database has
evolved and been a solution to collect data but now we want to move
the tables over to sql 2000. We have successfully done that but are
now having difficulties with an ODBC connection. Initiall linking the tables is not an issue and I am able to connect with no problem.
However after closing down the ODBC database and then going back in
the ODBC connection doesnt get refreshed and errors out when you try
to open the table. It then asks to redo connection. Because we have
over 40 people in this file, we want to make the ODBC connection
authenticate perhaps in the autoexec macro or the main form Load event so people dont keep getting this error.

Does anyone know if there is a way to refresh the ODBC connection each time you logon prompting for user name and password on the main form
load event or something. To have people go into the refresh linked
tables ect...would be way over their head.

Thanks,
Paul