Deploying

[Always_Learning]

Hi There,

I need to deploy an Access db to about 8 PC's in a domain.
I use the "Linked Table Manager" to set the connection details to MS Sql Server using an ODBC data source.

My problem is when I have it running on 1 PC in the domain I thought I could just copy it and put it onto other PC's and it would just connect to the database with no issues, but I get an ODBC error and I need to re-create the ODBC source again on that PC.

How can I deploy the Access DB with out having to do the ODBC settings again.

Thanks for your help.

Best Regards,

 

[pbaldy]

I create a File DSN on the SQL Server machine, and link using that.

 

[Galaxiom]

DSN issues can be eliminated entirely by using DSN-less connections. The ODBC connection string is held in the Connect property of the table link.

The down side is that each link must be edited if the data source moves, though this can be done quickly with a VBA loop. The file DSN has the advantage that it can be easily updated for everyone in one place.

Either way it is far better than installing a User or Machine DSN on each computer.

 

[Always_Learning]

Hi There,

Thanks for the replies.

If I create the file DSN how do I use it from the relevant PC's. I mean once set up how do I find it and associate it with the Access DB.

Thanks again for your help I appreciate it.

All the best,

 

[Galaxiom]

When you go through the ODBC linked table connection dialog just look for File DSN.

After connecting one table this way, have a look at its connect property if you want to script the changes to all the other tables.

 

[Always_Learning]

Hi Galaxiom,

Thanks for the reply.

I need to get this straight in my head.

I go to the main server that has the MS Sql Server database on it and create a file DSN.

I then go to one of the domain PC's, open the Access DB and in the Link Table Manager I associate the tables with the file DSN I created on the server.
The domain PC will be able to see the File DSN on the server with no problem?

Thanks again.

Best Regards,

 

[Galaxiom]

You can create the File DSN in the connection dialog on a PC. Note the path it is saved in. Browse to the server of just create it locally and copy the DSN file to the server.

Then link a table, following the File DSN branch of the dialog to the folder where it is saved on the server.

 

[Always_Learning]

Which authentication should I use Network or Sql Server, or doesn't it matter.

All the best,

 

[Always_Learning]

I have tried to do this but it's not working.

The steps I took were:
went to the database server and created a file DSN called TestDSN.dsn

Went to a domain pc, went into the Access DB / Linked File Manager / tried to find the DSN I set up on the server but could not get to it's location directory.
I can find it ok when I am in the server, it's located in Documents but I can't get to it from a domain PC.

Not sure where I go from here, obviously missing something.

Any pointers?

Best Regards,

 

[pbaldy]

I put it in a shared but hidden folder named Filedsn on the server. When linking, I type:

\\ServerName\Filedsn$\

into the DSN Name box, which will then bring up the DSN's in the folder named Filedsn.

Using a DSN-less connection is on my list of things to try, but I always seem to be short on time. :/