ODBC Connection between Access and SQL Server

[RTR]

I have a SQL Server database and use Access as a front-end. <br>
When setting up the ODBC drivers we selected System rather than Machine data source. We need to access the database from more than one client workstation. We have found that the connection to the SQL Server database can only be made from the machine and the login at which the ODBC connection was initially set up.<br>
Could anyone please tell me how to set up the ODBC connection for access from more than one workstation, and more than one login?<br>
<br>
Thanks very much, RTR

 

[databarn]

RTR,<br>
<br>
Sorry, but you're going to have to set up the ODBC connection on each machine that requres access to the SQL Server database. The 'System/Machine' distinction is misleading. However, if you don't have any other/significant ODBC accesses set up on the other machines, it's a simple enough matter to copy the ODBC.INI file from the working machine to those machines. Just be watchful that you don't overwrite existing ODBC configs in the process.<br>
<br>
Or, you might write a VB/VBA/WSH applet to add the appropriate section to the file on the other machines (did that once with VB on a VAX network). For that matter, any programming/procedural language could be used: you could even do it with a batch file.

 

[FooDog]

RTR,<br>
<br>
Try looking at the RegisterDatabase method.<br>
<br>
I use something that looks like this in my autoexec code:<br>
<br>
DBEngine.RegisterDatabase "IRR", "SQL Server", True, "Database=IR" & vbCr & "Server=Eden"