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