Problem w/ linked tables - Access to SQL Express

[Garzaroger]

As per a previous recommendation, I'm trying to change my data into ODBC through SQL Express and use it through Access (front end). I already exported the tables from my original Access back-end DB to ODBC. I also created new links to these tables on my Access Front-end DB. When I work on the network server everything works fine, but the clients that open the front end receive an "ODBC connection failed" error. I even tried using administrator password and rights on the client computers with the same result.

Do I need to create a special connection on each client?
If so (excuse my ignorance), How?

 

[SiJP]

I'm assuming the SQL Express server is located on individual client machines?

I get the feeling that maybe you've left it on the server, therefore the client can't actually access 'see' the SQL server?

------------------------
Hit any User to continue

 

[Garzaroger]

You are correct.

I was under the impression that the client machines only needed a DSN driver to connect to the SQLEXPRESS server located on the server itself.

Every machine has Access, and when creating a system DSN driver, the SQLEXPRESS server appears as a choice for connection. The connection test result is successful however, when accessing the database and trying so view the linked tables there is a connection error.

I have to apologize for my lack of knowledge in the subject. I only recently started working with Databases and I'm having trouble understanding connection protocols!

I will try installing the SQL express on the clients for testing. Thanks for the help.

 

[SiJP]

Garzaroger,

Never apologize for a lack of knowledge in an area.. Hell I don't know what I'm talking about half the time!

Anyroad....

You should still be able to access the data, after all, you are connecting to a datasource using odbc.

SQL Express enables a client machine to 'attach' a database to open it up on there machine. It's like having MS Office installed to view an Access Database.

You could still 'see' the data in an Access database if you didn't have Access installed, through some other odbc software, or a VB program.

I suspect this is a case that the local client computer may not be able to see the server which the SQL database resides on.. if they can, check perhaps on security?

------------------------
Hit any User to continue