February 23, 2010 at 3:34 pm
I have a access mdb built on 2003 and works with a system ODBC connection (to connect to linked sql table). However, when we share it on a client with access 2007, it keeps trying to connect using windows authentication. Once I click OK on the error message, it brings up another window to enter in the credentials for the system ODBC. How can i make it default to the system ODBC?
March 2, 2010 at 6:07 am
My suggestion is to add code to a startup form that re-links all the tables with the connetion string of your choice. I run the startup form as a splash screen and close it after relinking the tables (in the timer event). I have the timer set for 2 seconds.
The example below uses a mySQL database, but you can easily update it for a SQL Server.
Private Sub Form_Timer()
On Error GoTo ErrTimer
Dim tbl As TableDef
Me.TimerInterval = 0
For Each tbl In CurrentDb.TableDefs
Select Case Left(tbl.Name, 4)
Case "msys", "uSys"
'skip it
Case Else
If Left(tbl.Name, 1) <> "~" Then
SysCmd acSysCmdSetStatus, "Relinking web table: " & tbl.Name
Me.lblRelinked = "Relinking web table: " & tbl.Name
Me.Repaint
'one way to do it
tbl.Connect = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};UID=ltdbuser;PWD=mericaxp1;SERVER=MySQLB3.webcontrolcenter.com;PORT=3306;OPTION=0;DATABASE=mekate"
'slight variation using a DSN file.
'tbl.Connect = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};UID=ltdbuser;PWD=mericaxp1;PORT=3306;SERVER=MySQLB3.webcontrolcenter.com;DATABASE=mekate;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\mekate.dsn;"
tbl.RefreshLink
Else
DoCmd.DeleteObject acTable, tbl.Name
End If
End Select
Next tbl
Me.lblRelinked = "Done!"
Me.Repaint
ExitTimer:
SysCmd acSysCmdClearStatus
DoCmd.Close acForm, Me.Name
Exit Sub
ErrTimer:
MsgBox "Error: " & Err.Number & ", " & Err.Description & " in " & Me.Name & " on Form_Timer."
Resume ExitTimer
Resume
End Sub
March 3, 2010 at 1:13 am
How come you delete the tables after relinking them?
March 3, 2010 at 5:41 am
grovelli-262555 (3/3/2010)
How come you delete the tables after relinking them?
the delete is in the "else" and it removes links to non-existant tables.
March 3, 2010 at 6:16 am
Thanks! 🙂
March 3, 2010 at 11:01 am
The definition of the ODBC source defines whether to use Integrated authenitication or SQL Server authentication on the second tab.
If you have specified Integrated and it displays an error and asks for authentication information typical causes are:
1. The ODBC source isn't defined on the users PC - each PC has to have the source defined
2. The ODBC source was defined as a user specific source under someone else's userid
3. The server can not be found and/or the user does not have access to the server
4. The user does not have access to the database that is being opened by default.
5. If you are opening a specific table, the user doesn't have access to the table.
March 4, 2010 at 9:18 am
royce.bacon (3/3/2010)
The definition of the ODBC source defines whether to use Integrated authenitication or SQL Server authentication on the second tab.If you have specified Integrated and it displays an error and asks for authentication information typical causes are:
1. The ODBC source isn't defined on the users PC - each PC has to have the source defined
2. The ODBC source was defined as a user specific source under someone else's userid
3. The server can not be found and/or the user does not have access to the server
4. The user does not have access to the database that is being opened by default.
5. If you are opening a specific table, the user doesn't have access to the table.
The ODBC source was added to the user's local machine under System DSN tab with SQL Server Authentication. The login was tested successfully. On the Access db, there is a linked table that uses that ODBC name, but yet it keeps trying to connect using Windows auth. Is there a setting within Access to force to use this ODBC?
March 4, 2010 at 9:46 am
is250sp (3/4/2010)
royce.bacon (3/3/2010)
The definition of the ODBC source defines whether to use Integrated authenitication or SQL Server authentication on the second tab.If you have specified Integrated and it displays an error and asks for authentication information typical causes are:
1. The ODBC source isn't defined on the users PC - each PC has to have the source defined
2. The ODBC source was defined as a user specific source under someone else's userid
3. The server can not be found and/or the user does not have access to the server
4. The user does not have access to the database that is being opened by default.
5. If you are opening a specific table, the user doesn't have access to the table.
The ODBC source was added to the user's local machine under System DSN tab with SQL Server Authentication. The login was tested successfully. On the Access db, there is a linked table that uses that ODBC name, but yet it keeps trying to connect using Windows auth. Is there a setting within Access to force to use this ODBC?
That is controlled by the link to the table. The information about how the link is done is stored in the access application and not on individual PC's. If multiple people are using the database from the network then every user must have basically the same ODBC source defined on their PC's.
You might try refreshing the links on all the tables to insure they have the new link information. There is an option under Tools, Database Utilities called Linked Table Manager. Using that you can tell Access to refresh all of the links to the tables. Do this on the PC where you are having the problems.
March 4, 2010 at 10:27 am
That is controlled by the link to the table. The information about how the link is done is stored in the access application and not on individual PC's. If multiple people are using the database from the network then every user must have basically the same ODBC source defined on their PC's.
You might try refreshing the links on all the tables to insure they have the new link information. There is an option under Tools, Database Utilities called Linked Table Manager. Using that you can tell Access to refresh all of the links to the tables. Do this on the PC where you are having the problems.
Ok so i went to the user's pc, opened the Access file, went into Linked Table Manager. It shows the one linked table. I checked the checkbox, click OK to refresh the link, it then pops up a message saying connection failed using windows login. I click ok and it brings up a window to enter in password for the ODBC. Enter in password, click OK, and it says linked table successfully refreshed. However, when I close the Access file and re-open, it tries to connect using windows auth again.
March 4, 2010 at 11:24 am
is250sp (3/4/2010)
That is controlled by the link to the table. The information about how the link is done is stored in the access application and not on individual PC's. If multiple people are using the database from the network then every user must have basically the same ODBC source defined on their PC's.
You might try refreshing the links on all the tables to insure they have the new link information. There is an option under Tools, Database Utilities called Linked Table Manager. Using that you can tell Access to refresh all of the links to the tables. Do this on the PC where you are having the problems.
Ok so i went to the user's pc, opened the Access file, went into Linked Table Manager. It shows the one linked table. I checked the checkbox, click OK to refresh the link, it then pops up a message saying connection failed using windows login. I click ok and it brings up a window to enter in password for the ODBC. Enter in password, click OK, and it says linked table successfully refreshed. However, when I close the Access file and re-open, it tries to connect using windows auth again.
I'd suggest deleting the linked table and re-linking it.
Also, make sure the ODBC data source is set up right. If you want to use SQL Server authentication then on the second screen you have to have the option for With SQL Server authentication choosen and you have to supply a Login ID and Password in that screen. If no Login ID and password are supplied then Access would have to ask for one each time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply