ODBC, trusted connection for multiple users

[KathyL]

I have an Access DB with a few ODBC links to some tables in an external 3rd party My SQL DB. I'm able to use a similar ODBC connection to this same SQL db with Crystal Reports and multiple users have no problem running their Crystal Reports.



However, Access is only allowing me to configure one PC client to have the ODBC trusted connection work... if you try to configure a 2nd PC, it becomes the one with the allowed connection, and the 1st PC no longer works.

Can anyone tell me the clue to making ODBC using trusted connections work for multiple users in the same database?

Thanks!

[pkstormy]

This sounds like you might have the ODBC named slightly different for the 2 users. If the ODBC name is not EXACTLY the same for both users, you'd have one user which you fix it for but then the other user has problems. When you fix it for the 2nd user, the 1st user then has problems. I spent months with the same situation as yours only to find out the ODBC has to be named exactly the same for both users. Also make sure you've checked the "Save Password" when/if linking tables.

I actually have code which creates the ODBC automatically. I believe the attached file is what I use. Just change the code part in the module (GetLinkedTables function) to point to your server and if the user's login has permissions to create an ODBC, it will create it automatically with the correct ODBC name when the user opens the app (just import the module and call it when your main form opens - I believe the function is called: GetLinkedTables - but you can see what is called when the MainForm opens).

(I think the SystemDSNCreate module is optional but I'm not 100% sure. I dug this up in my archives and created it about 10 years ago.)

[KathyL]

Thank you pkstormy.
I have looked at your DB and code. I'll do some checking into ODBC names.
I came up with another solution yesterday that I'll probably stick with.
I've set up Windows Scheduler so that I can have it automatically run a new/separate small database periodically during the day. This will be done on one PC, the same PC with our ODBC connection. We'll move it to a PC which is on all the time and is not normally used by any user. Admins and a specific user will be able to check it anytime they wish.

This new/separate small database will have my ODBC inked tables in in. I will have the database load a form upon execution, and I have a form Load event which will run a series of purge queries on some local tables, and then a series of append queries which will bring the data from the ODBC tables into the local tables. I also update a single table to show the most recent date/time of "my" download, so admins and users will know how up to date the data is.

Those local tables are linked from the main database. All users who have access to the main database will immediately have new updated data which was downloaded from the ODBC tables. So none of the other users will need to have ODBC links at all.

We also have a similar process which is in the MY SQL database/vendor which downloads other data every 15 minutes. I have no control ovr the My SQL database/vendor/download, but I will have control over what I choose to downlload.

I've already tested my alternate solution and it worked rather neatly. Simple, no VB code needed at all. I'm not against VB code because I use it whenever needed.

Thanks again for your answer, I will also be considering it.

[pkstormy]

Good to hear you found a solution KathyL. Thanks for the update.

[pkstormy]

KathyL,

Note that another option that I usually prefer versus scheduling a Task to open an actual mdb and relying on it to run an OnLoad event of a form is to schedule a Task to just execute a vb script which runs directly against the SQL Server db itself or MySQL Server db (it's a little more reliable method if you're familiar with vb scripting). I will schedule tasks to run a vb script which then has code such as the following in the *.vbs file:

Set WSNet = CreateObject("WScript.Network")
varUserName = WSNet.UserName
Set WSNet = Nothing
Set MyConn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
'modify the below lines to use/tap into an Oracle or other MySQL driver
MyConn.open = "DRIVER={SQL Server};"_
& "SERVER=SQLSERVER;"_
& "DATABASE=DBName;"_
& "OPTION=35;"
sql_query = "UPDATE dbo.MyTable SET SomeField ='" & "SomeValue" & "' WHERE ID ='" & ID & "'"
' or put whatever SQL code in place of the statement above
'ex: set sql_query = MyConn.Execute("DELETE * from MyTable")
'ex: set sql_query = MyConn.Execute("INSERT INTO MyTable (Field1, Field2) SELECT
MyAccessLinkedOracleTable.Field1, 'MyAccessLinkedOracleTable.Field2
'FROM MyAccessLinkedOracleTable")
MyConn.Execute sql_query
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing

or against an mdb file without opening the actual mdb itself using ADO:

Set WSNet = CreateObject("WScript.Network")
varUserName = WSNet.UserName
Set WSNet = Nothing
Set MyConn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HelpForOthers\MyMDBFileName.mdb"
Set RS = MyConn.Execute("UPDATE MyTableName SET SomeFieldName ='" & "SomeValue" & "' WHERE ID ='" & SomeID & "'")
' or put whatever SQL code in place of the statement above
'ex: set RS = MyConn.Execute("DELETE * from MyTable")
'ex: set RS = MyConn.Execute("INSERT INTO MyTable (Field1, Field2) SELECT MyAccessLinkedOracleTable.Field1, 'MyAccessLinkedOracleTable.Field2
'FROM MyAccessLinkedOracleTable")
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
retval = msgbox("Table Updated")

or if you can execute php scripts on the box against the SQL or MySQL server:

<?php
$dsnname = 'MyODBCDSN';
$user = 'sa';
$password = 'password';

$connection = odbc_connect($dsnname, $user, $password);
//Passing IDField value to php script
$IDField = $_REQUEST['IDField'];
$ETime = DATE("m/d/y h:i:s A");
$query = "UPDATE MyTableName SET MyTableName.EndTime = '$ETime' WHERE MyTableName.IDField = '$IDField';";

or
$query = "INSERT INTO MyTableName (IDField, Field1, Field2, Field3, Field4, Field5)
SELECT '$IDField', '$SomeValue1', '$SomeValue2', '$SomeValue3', '$SomeValue4', '$ETime';";

$rs=odbc_exec($connection,$query);
odbc_close($connection);
?>

or another method to connect via a php script:

<?php
//Enter IP Address below of SQL Server or MySQL Server
$server = '128.100.100.100';
$database = 'MyDBName';
$user = 'sa';
$password = 'password';
$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);
if (!$connection) {
echo "Connection Failed";
} else {
echo "Connection Made!";
}
?>