Setting up a data souce

[Rowen]

I'm sure this is a bit of a silly question, but well if I don't ask I won't learn.

I've got an access front end to an SQLServer backend, the two were originally linked by upsizing the access db to SQLServer. However I've since added some new tables in the SQLServer end, and I'm trying to figure out how to set up the linked table in Access. I know to go to ODBC Datasources, to select a new one and select SQL Server however what I'm not sure is where I'm supposed to save this new data source.

The access file is one where people are going to be using it on different machines, am I going to have to set this connection up on each machine, or once they get a copy of the access file I'm working on, will this not matter? I am new to setting up linked tables so I really don't know how this all works.

 

[pbaldy]

You should be able to use the same DSN used when you upsized. What I do is set up a folder on the server and create File DSN's there. I then link the tables using the File DSN, which means I don't have to set up anything on the individual PC's.

 

[Rowen]

I've tried to find the original data source, but I've had no luck, so it's looking like I'm going to need to set up my own ODBC. But then I think I'm going to have an issue with passwords as at the moment the users just open the programme and can start using it, obviously I don't want to change that, but I'm worried that if I change the password in SQLServer then that'll give issues for the existing copies of the access files.

I've also done some playing around with trying to set up an ODBC and while I've set up the connection to link the table, if I go to link the table, and open the connection, I do not see my tables listed. I've tried setting up a view of the table and I still cannot see it in the list. So I'm not really sure what I've done wrong.

 

[Rowen]

Sorry for the double post, I was having issues with the connection when I posted, and my questions seems to have posted (at least I can see it), but the topic hasn't been updated.