Trying to reprogram my Msaccess 2000 to 2007, time to update.

Using MSSQL 2000 data tables.
When the programs start, I can push a button that allows me to re-connect to either the Real Data or Test Data. A simplified version of the code is as follows:

Public Function ReAttachBuildersTable() As Integer
Dim DB As Database
Dim tdf As TableDef

ReAttachBuildersTable = True
Set DB = CurrentDb()
Set tdf = DB.TableDefs("Builders")
tdf.Connect = "ODBC;DATABASE=JSEdata;UID=SA;PWD=;DSN=JSE_SQL"
tdf.RefreshLink
ReAttachBuildersTable = (Err = 0)
DB.Close
End Function

This has worked for years in Access 2000
I get an error 3151 in Access 2007 at the RefreshLink; eventhough the program can see the table and use it in list boxes and code using recordsets.
The Linked Table Manager works fine. I just cannot RefreshLink using code.
I think I have all the Trusted areas set to yes.

Any ideas?

Hi,

hmm I already posted a reply to this, not sure what happend to it.
Ok I dont know much about Access 2007 but I am wondering if DAO 3.6 support has been removed / minimised.

Try and use ADO instead

I have DAO 3.6 OL referenced but still no luck

Hi,

hmm I already posted a reply to this, not sure what happend to it.
Ok I dont know much about Access 2007 but I am wondering if DAO 3.6 support has been removed / minimised.

Try and use ADO instead

It would appear, at least in my Access 2007, that the default is actually NO checked reference to DAO or ADO. I just created a new ACCDB file and there was no DAO or ADO reference checked.

Don't know if these will provide any help, but you never know:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_22450147.html

http://www.webservertalk.com/archive136-2007-8-1895352.html

I've tried various references but no luck.

Microsoft wants $259 just to talk to me.

Should I pay?

Eureka!

Original line:
tdf.Connect = "ODBC;DATABASE=" & MySQLdb & ";UID=SA;PWD=;DSN=" & MyDSN

Working line:
same as above but with: "DRIVER={SQL Server};SERVER=JSE2005;"

I guess MS Access 2007 needs the DRIVER and SERVER clauses for whatever reason.

ttfn