Brandon Garnett
asked on
Deleting then Re-Adding Table Defs in MS Access VBA
Hello,
I have a FE that when it loads, it calls a function that goes through a Local access table that has a list of all the Linked Tables linked to SQL Server. It deletes the TableDefs for each table and then re adds the TableDef back so that everything stays fresh, as i am using DSNLess connection.
Below is the two functions which do so. DSNLessConnect Calls AttachDSNLessTable
The Acutal deleting and readding of Tabledefs happens in AttachDSNLessTable
The problem i face is that not every time or with every one here but occasionally my Error handler runs Saying "Attempts to connect to SQL Server failed. The system returned the following error: Table 'Table Name' already Exists"
It does not give an error number even though it says to in my error handler
Does anyone know why occasionally I get that message as an error and how i can prevent it from happening? Any ideas/Suggestions appreciated.
I have a FE that when it loads, it calls a function that goes through a Local access table that has a list of all the Linked Tables linked to SQL Server. It deletes the TableDefs for each table and then re adds the TableDef back so that everything stays fresh, as i am using DSNLess connection.
Below is the two functions which do so. DSNLessConnect Calls AttachDSNLessTable
The Acutal deleting and readding of Tabledefs happens in AttachDSNLessTable
The problem i face is that not every time or with every one here but occasionally my Error handler runs Saying "Attempts to connect to SQL Server failed. The system returned the following error: Table 'Table Name' already Exists"
It does not give an error number even though it says to in my error handler
Does anyone know why occasionally I get that message as an error and how i can prevent it from happening? Any ideas/Suggestions appreciated.
'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
'//Important Notes
'//There are two tables in the front end, these tables contain the server side name and linked name of all SQL Tables linked
'//to database, one is the primary table pointing to the primary server, the other is a secondary server in the event the primary is down
'//the tables links are refreshed every time the FE Loads, if tables are added or removed the appropriate changes need to be noted
'//in the two tables hcc_PrimarySQL and hcc_tblSecondarySQL and the version number updated so clients update when they are opened.
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
Sleep 50
Exit For
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
AttachDSNLessTable_Exit:
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
DoCmd.Hourglass False
MsgBox "Attempts to connect to SQL Server failed. The system returned the following error: " & Chr(13) & Err.Description & " Error Number: " & Err.Number
Resume AttachDSNLessTable_Exit
End Function
Public Function DSNLessConnect()
On Error GoTo Err_DSNLessConnect
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strLocal As String
Dim strsql As String
Dim strServer As String
Dim strDatabase As String
Set db = CurrentDb
Set rs = db.OpenRecordset("hcc_tblPrimarySQL")
rs.MoveFirst
strLocal = rs!LocalTable
strsql = rs!SQLTable
strServer = rs!Server
strDatabase = rs!Database
'Check to see if we are connected to the primary server.
If AttachDSNLessTable(strLocal, strsql, strServer, strDatabase, "", "") Then
'We are connected so we will refreshall the tables
rs.MoveNext
Do Until rs.EOF
If rs!NeedsRefreshed = True Then
strLocal = rs!LocalTable
strsql = rs!SQLTable
strServer = rs!Server
strDatabase = rs!Database
Call AttachDSNLessTable(strLocal, strsql, strServer, strDatabase, "", "")
With rs
.Edit
!NeedsRefreshed = False
.Update
.MoveNext
End With
Else
rs.MoveNext
End If
Loop
Else
'// Not okay.We will try the secondary database
Dim rsAlt As DAO.Recordset
Set rsAlt = db.OpenRecordset("hcc_tblSecondarySQL")
rsAlt.MoveFirst
strLocal = rsAlt!LocalTable
strsql = rsAlt!SQLTable
strServer = rsAlt!Server
strDatabase = rsAlt!Database
If AttachDSNLessTable(strLocal, strsql, strServer, strDatabase, "", "") Then
'We have connection to secondary Database
MsgBox "The primary database " & rs!Server & " is not connecting, please contact sys admin." & Chr(13) _
& "Connecting to backup server!", vbOKOnly, "Connection Error"
rsAlt.MoveNext
Do Until rsAlt.EOF
If rsAlt!NeedsRefreshed = True Then
strLocal = rsAlt!LocalTable
strsql = rsAlt!SQLTable
strServer = rsAlt!Server
strDatabase = rsAlt!Database
Call AttachDSNLessTable(rsAlt!LocalTable, rsAlt!SQLTable, rsAlt!Server, rsAlt!Database, "", "")
With rsAlt
.Edit
!NeedsRefreshed = False
.Update
.MoveNext
End With
Else
rsAlt.MoveNext
End If
Loop
Else
On Error Resume Next
DoCmd.Hourglass False
'Could not connect to either database, catastrophic error, close program after notifying user
MsgBox "No Connection can be made to primary or backup database, please notify sys admin."
DoCmd.Quit
End If
End If
Exit_DSNLessConnect:
On Error Resume Next
db.Close
rs.Close
rsAlt.Close
Set db = Nothing
Set rs = Nothing
Set rsAlt = Nothing
strLocal = ""
strsql = ""
strServer = ""
strDatabase = ""
Exit Function
Err_DSNLessConnect:
DoCmd.Hourglass False
MsgBox "An Error Occurred in the DSNLessConnect Function; Module: hcc_modDSNLessConnection " & _
vbCr & vbCr & "Error Number: " & Err.Number & vbCr & vbCr & "Error Desc: " & Err.Description, vbInformation, "PLEASE TAKE A SCREENSHOT OF THIS ERROR!"
Resume Exit_DSNLessConnect
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for your help.
I made the changes you suggested to the code.
As this error only happens occasionally (and generally not with me When i try to debug it) i will leave this question open for a few days so there is time to see if that will fix it with all of my people.
Thanks again, If you have any other ideas in the mean time, please post so i can try that as well.
I made the changes you suggested to the code.
As this error only happens occasionally (and generally not with me When i try to debug it) i will leave this question open for a few days so there is time to see if that will fix it with all of my people.
Thanks again, If you have any other ideas in the mean time, please post so i can try that as well.
I agree with JDettman. The error message you observe should disappear when you refresh your tabledefs.
Example:
Example:
dbEngine(0)(0).TableDefs.Refresh
ASKER
Adding the Refresh Command Helped. It got rid of the original Error it looks like however, some staff are receiving new errors. The first is "Not a valid Bookmark" Error Number 3159; as you can see im not using Bookmarks in this section of code so why am i getting this error? the 2nd error staff is receiving is "Record is Deleted" Error Number 3167; Im not quite sure why im getting this error either. Can anyone help?
Sorry I haven't responded sooner, I was on vacation.
Add a timestamp column to the SQL Table. You don't need to do anything with it, just add it to the table and then drop and add the table links again.
This turns on row versioning in in SQL Server, which JET will use to keep track of records in recordsets if it sees it.
Jim.
Add a timestamp column to the SQL Table. You don't need to do anything with it, just add it to the table and then drop and add the table links again.
This turns on row versioning in in SQL Server, which JET will use to keep track of records in recordsets if it sees it.
Jim.
Dim db As DAO.Database
Set db = CurrentDB()
For Each td In db.TableDefs
If td.Name = stLocalTableName Then
db.TableDefs.Delete stLocalTableName
db.TableDefs.Refresh
Exit For
End If
Next
Set db = nothing
If there is no chance of another user modifying the tabledefs collection while this is being done, you can omit the refresh in the above.