Conecting Access to SQL in Windows 7

[RSteph]

I've got a program with a Microsoft Access "Front End" for forms, reports,
queries, etc. That I am trying to move from having attached to an Access
"Back end" to a SQL "Back End" database. I've put together some code that
worked on my old Windows XP computer, but doesn't seem to work on my new
Windows 7 computer.

I've gone into the ODBC Connections screen through Administrative Tools in
the Control Panel on the Windows 7 computer. I can setup the SQL connection
there, it connects to the SQL Database, and tests successfully. For some
reason in Access though, I cannot connect to the database with my code.

There error I get is:

Run-time error '3219'
Invalid Operation


The code in the Access form is (After building the registry key):

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblODBCDataSources ORDER BY
LocalTableName", dbOpenDynaset, dbSeeChanges)

While Not rs.EOF
strTblName = rs!localtablename
If left(strTblName, 3) <> "tbl" And left(strTblName, 3) <> "stg"
Then
On Error Resume Next
CurrentDb.QueryDefs.Delete strTblName
Err.Clear
On Error GoTo 0
End If
strConn = "ODBC;DSN=" & DataSourceName & ";APP=The
Associate;DATABASE=" & Me.txtDatabase & ";UID=" & Me.txtUserName & ";PWD=" &
Me.txtPassword & ";TABLE=" & rs!ODBCTableName
If DoesTblExist(strTblName) = False Then
Set tdf = CurrentDb.CreateTableDef(strTblName,
dbAttachSavePWD, rs!ODBCTableName, strConn)
Me.cmdReAttach.StatusBarText = "Reattaching " & tdf.Name
CurrentDb.TableDefs.Append tdf
Else
Set tdf = db.TableDefs(strTblName)
tdf.Connect = strConn
Me.cmdReAttach.StatusBarText = "Reattaching " & tdf.Name
tdf.RefreshLink
End If
rs.MoveNext

Wend
rs.close
Set rs = Nothing

It errors on the line : "tdf.Connect = strConn"

Any help would be greatly appreciated.

 

[Sylvain Lafontaine]

If you Windows 7 is 64 bit, you must the 32 version of the ODBC panel to
create your DSN because Access run under the emulated 32 bit mode and cannot
access registries set in the 64 bit part.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

Sorry, too fast on the keyboard: for the 32 version of the ODBC panel, see:
http://support.microsoft.com/kb/942976

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[RSteph]

In this case, it is a 32 bit version of Windows 7. (According to the Computer
properties).

 

[Sylvain Lafontaine]

Are you able to connect using a blank new database file directly created on
Windows 7?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)