Conecting Access to SQL in Windows 7


R

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.
 
Ad

Advertisements

S

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)
 
R

RSteph

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top