Linking ODBC Tables for Multiple Users

[nogreatnamesleft]

I have created a database on a shared drive which links some tables from an
SQL server. I set up the link to the SQL server and select 'System'. However,
it appears that this only creates the ODBC table on my local machine. So when
users on other machines connect to the database their machines are not set up
to connect to the SQL table. How do I fix this so I don't have to set up ODBC
tables on every machine?

Thanks,
Dan

 

[Jeff Boyce]

One approach would be to create an ODBC driver that connects (hint: use UNC
rather than "share" name ... not all users name their shares the same way)
to your SQL-Server data, then put that ODBC driver on a network share that
all users PCs can "see".

Next, drop all your linked tables from your (development) copy of the
front-end, then re-link, using that new ODBC from the network. Now your
application/front-end knows to look to the network first to get the ODBC,
then follow it to the SQL-Server tables.

Finally, install a copy of that front-end on each user's PC (hint#2: don't
"share" a front-end ... that causes corruption and slows down your network).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[nogreatnamesleft]

Jeff, this sounds real good, but how do I create an ODBC driver?!?! Actually
I don't understand half of the solution - could you clarify please? Thanks!

 

[Banana]

Jeff, this sounds real good, but how do I create an ODBC driver?!?! Actually
I don't understand half of the solution - could you clarify please? Thanks!

Strictly speaking you don't create an ODBC driver. You use an ODBC
driver and you may want to create a DSN (not required, but for first
time, it'll be convenient).

In 2003 and earlier, it's

File -> Get External Data -> Link Tables

In the dialog, ignore the files, and select "ODBC Databases" as the file
type. This will then close the file dialog and open a ODBC DSN dialog.


If you're using 2007, click the External Data tab on the ribbon, then
click "More.." button to select ODBC databases.

The following steps are for both 2002/2003 and 2007:

Click "New..." to create a new DSN. Select the appropriate driver & fill
in the required information and you can then link to tables and treat
them as if they were local tables.

Some notes:

1) Not all ODBC sources have drivers installed. SQL Server has one
installed already but if you're using a different server, you may need
to download their ODBC driver. Note that there may be more than one
driver (Oracle is a case in point, there's a driver from Microsoft and a
driver from Oracle, both with their ramifications to reckon)

2) You need to know a minimum of three things: the driver you need, the
server's name or location, the databsae name or location. You may also
need permissions as well.

HTH.

 

[nogreatnamesleft]

I'm really looking for something like using SqlConfigDataSource in Access
code to create the ODBC datasource automatically when Access is run.

 

[nogreatnamesleft]

I set it up using this code:

Option Explicit
Const ODBC_ADD_SYS_DSN = 4 'Add data source
Const ODBC_CONFIG_SYS_DSN = 5 'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6 'Remove data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
hwndParent As Long, ByVal fRequest As Long, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Long

Sub on_start()
Dim DSN_NAME As String
Dim Server As String
Dim Db_Path As String

DSN_NAME = "DB_DSN"
Server = "DB_Server"
Db_Path = "DB"
Call Build_SystemDSN(DSN_NAME, Server, Db_Path)

End Sub

Function Build_SystemDSN(DSN_NAME As String, Server As String, Db_Path As
String)

Dim ret%, Driver$, Attributes$

Driver = "SQL Server" & Chr(0)
Attributes = "DSN=" & DSN_NAME & Chr(0)
Attributes = Attributes & "Description=SQL Data" & Chr(0)
Attributes = Attributes & "Server=" & Server & Chr(0) & "Database=" &
Db_Path & Chr(0)
'Attributes = Attributes & "UID=na_mex_ro" & Chr(0) & "PWD=na22mex22ro" &
Chr(0)
Attributes = Attributes & "AutoTranslate=Yes" & Chr(0)
Attributes = Attributes & "Trusted_Connection=No" & Chr(0)


ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)

'ret is equal to 1 on success and 0 if there is an error
If ret <> 1 Then
MsgBox "DSN Creation Failed"
End If

End Function

http://msdn.microsoft.com/en-us/library/aa177860(SQL.80).aspx

The only thing I can't figure out is how to write the user ID and password
into it. The SQL Server does not take Windows Authentication, just an SQL
server password I was assigned for Read-Only. Any ideas?

 

[Banana]

I'm really looking for something like using SqlConfigDataSource in Access
code to create the ODBC datasource automatically when Access is run.

I'm not sure what is this "SqlConfigDataSource", but I'll take a stab.

If by this, you want it to be total automatic configured and perhaps
self-healing, then you may want to take a look at Doug J. Steele's
DSN-less connection.

But if by this, you want users to be able to configure the ODBC driver
(which kinds of scares me...), you can just either do a OpenDatabase
with incomplete connection string and it'll open a ODBC dialog. If you
specify a SQL server driver, it'll open the SQL Server driver, if my
memory serves me correctly. Note that Access will cache and re-use the
same connection as long three things are same: driver, server and
database. No need to store passwords & username anywhere else. Or better
yet, create your own unbound form to receive the necessary parameters
and pass it into Doug's DSN-less connection code.

 

[nogreatnamesleft]

Banana, thanks for the reference - has some interesting stuff I'll need to
look into. However, I found my above solution works without a password. Not
sure why yet, but it works!