PDA

View Full Version : linking to SQL Server as ODBC interface


DBinPhilly
11-20-2009, 02:43 PM
I haven't used SQL Server as a back end to a standard MDB database in about 10 years, so pardon my ignorance.

I have an instance where I plan to write a Microsoft Access Project but in the interim I have imported all the tables from an existing MS Access mdb database into SQL and have linked the SQL tables to a new version of the MDB database - my intent is to avoid going through a number of data conversions. Capture the data on SQL and forget about the old mdb database.

BUT --- I imported the data to the SQL Server. On a workstation I copied all of the objects to a new mdb database. I then linked the SQL Server database using ODBC connectivity. All things worked fine.

But when I copy a version of the new front-end to other workstations, those work stations do not 'see' the SQL tables. I've tried going through the connectivity linking individually but they can't link for some reason.

Why would one work station 'see' the SQL Server and other work stations not see it?

Any suggestions?
OBP
11-21-2009, 05:40 AM
I have never tried doing what you are doing, but I might be able to ask those "Stupid Questiosn" that help you Problem Solve it.

Can the work Stations see the SQL Server outside of Access?
i.e. do they have read/write access to the Server and folders etc?
Do the tables actually show up in the Front End when copied to the other workstations, but can't be "re-Linked" using the Link Manager?

If they can't be re-linked do you get any error messages?
Do you need to set up the ODBC connection in each "Copy" of the front end?
asingh
11-23-2009, 05:28 AM
What exact error you getting when you try to re-attach the object on separate workstations.

Could you ping the SQL server from the workstations, and check the connectivity...?
DBinPhilly
11-23-2009, 05:55 AM
The problem is solved. I went on line last night and discovered a step I had missed.

If I need to do this again 10 years from now, I'll probably forget it again.
CreganTur
11-23-2009, 07:30 AM
Please tell us what step you missed so that others can learn from this.

Thanks,