June 8, 2007 at 6:22 am
I had a working query into my SQL 2000 database using Microsofy access usling tables that are linked using an ODBC connections. I recently when to link a new table to the query and when selecting the ODBC Databes () the "Select Data Source" menu no longer pops up. This problem seems to be machine specific, I searched Microsoft's supoort pages and the web and did not find a solution.
Bruce
June 11, 2007 at 8:37 am
Is this a new machine? If so, sometimes not all the wizards get installed when installing Access. You might try going to the add/remove programs and click change for the access installation and ensure that you have all the wizards installed.
If it isn't that, then you may have an MDAC issue -- you might try reinstalling the appropriate MDAC pack.
June 11, 2007 at 11:19 am
This PC is aproximately 6 months old and has been able to link the tables in the past. I removed MS Access 2003 and reinstalled, I also remove the ODBC connection and reinstalled. I tried to install MDAC from MS and during the install it told me that MDAC was part of the operating system. I then did a repair on the XP SP2 operating sytem. Still having the same problem.
Bruce
June 12, 2007 at 7:26 am
Is the Access file an MDB or MDE?
June 12, 2007 at 7:50 am
Can you create a dsn from the Control Panel (data source), completely outside of Access?
Can you retrieve data from that table in the SQL database from within Excel?
Have you made sure that your Access 2003 reinstall included all the bells & whistles, including the data converters?
Does the signed on user have admin privileges?
Many years ago, I had this happen with a machine running NT and Office 97, and we finally found it was a wizard that wasn't installed... an update to IE, somehow uninstalled the wizard. We practically had to wipe the machine and start over before we figured out the wizard part -- our IT dept. didn't realize that all those wizards were needed, so they habitually left them out... so the third time they reinstalled Access I watched and caught what they were(not) doing and we selected all of the options except the sample databases and findfast... and then it started working again.
June 14, 2007 at 6:19 am
The database is a MDB. There was an virus with IE7 that required a complete reformat on this PC so I did a reinstall of XP Pro and Access 2003 (complete). We tried the access query and it works, but I still have the same problem of being unable to create a new ODBC link. The user has admin read/write priviledges. I set up the user on my PC and he was able to create a new ODBC link. I would normally think that this was a PC issue (Dell Latitude 820), but the fact that I did a complete reinstall leaves me with a cloud of doubt. Any suggestions?
June 14, 2007 at 8:47 am
If the same user on a different machine can perform the task than the problem is with the PC.
Suggestion: Open the database on your machine, make the link there, and give it back to him. The DSN is only used in the initial setup of the link, and it is using the same SQL Server both places, using trusted authentication, it should work on the other desktop as well.
If you're not using trusted authentication, then be sure to use the correct user when creating the link on your desktop.
Alternatively, you can create the link in code. Here's some vba code that you can use as a template...Please watch linebreaks. You call it thusly: AddSQLConnection(False, "dbo.tblMyTable")
Function AddSQLConnection(IsLocal As Boolean, tblName As String)
Dim sCONN As String
If IsLocal = False Then
'!!! MODIFY CONNECTIONSTRING TO YOUR PARAMETERS !!!
sCONN= "DRIVER=SQL Server;SERVER=SERVER1;DATABASE=HAData;Trusted_Connection=Yes"
Else
sCONN = "DRIVER=SQL Server;SERVER=(local);APP=Microsoft Office XP;WSID=DELL;DATABASE=HAData;Trusted_Connection=Yes"
End If
Dim tdef As DAO.TableDef
Dim qdef As DAO.QueryDef
Set tdef = CurrentDb.CreateTableDef(Replace(tblName, ".", "_"))
tdef.Connect = "ODBC;" & sCONN
tdef.SourceTableName = tblName
CurrentDb.TableDefs.Append tdef
Set tdef = Nothing
End Function
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply