Login Access 2007 with ODBC linked table from Oracle

Question

Hi, I'm migration Access 2003 to 2007. There are a lot of linked table from Oracle. I create a ODBC point to Oracle instance, then in Access I link all table through ODBC. I use this code login, so the "Oracle ODBC Driver Connect" dialog box would not popup to ask password:

  strConnect = "ODBC;DATABASE=" & strFCPD & ";DSN=PBRIS;UID=xxxxx;PWD=xxxxx"
  Dim wsp As Workspace
  Set wsp = DBEngine.CreateWorkspace("xxx", "xxx", "xxx", dbUseODBC)
  Set dbs = OpenDatabase("", False, True, strConnect)   ' connect via regular ODBC
  dbs.Close

It works fine in 2003 but not in 2007. What's wrong here? What should I do?

Answer 1

Does Access 2007 use a different ODBC driver than Access 2003? If so you'll need to create a new DSN which uses the Access 2007 driver. It looks like the existing DSN is named "PBRIS". Go into the ODBC Administrator and create a new DSN using the System DSN tab of the administrator tool, and specify the Access 2007 driver instead of the Access 2003 driver. You'll probably want to name it differently - perhaps something like "PBRIS2007". You'll need to change your code which builds the connect string to specify the new data source name.

I hope this helps.