G
GLT
Hi,
We initially had a process that ran in Excel VBA that checked our remote
servers and then updated an MS Access database.
I have now moved the VBA from Excel to MS Access.
So far the process seems to work OK, but none of the tables that should have
records added after process has run have any records.
I have stepped through the code (there are a lot of loops) and the record
updating (.addnew) does get processed.
I have also added message boxes to print out the values of the fields to be
updated and the values are fine.
Here is where all the DIM statement defined for the DB objects:
Dim dbBackupRecord As DAO.Database
Dim qdfActiveServers As DAO.QueryDef
Dim qdfClusterServers As DAO.QueryDef
Dim tbClusterCheck As DAO.QueryDef
Dim rsServerList As DAO.Recordset
Dim rsClusterList As DAO.Recordset
Dim rsBackups As DAO.Recordset
Dim rsUBItable As DAO.Recordset
Dim rsIncidentLog As DAO.Recordset
Dim DatabaseName As String
This is the current DB (this was orginally in the Excel code so I left it)
DatabaseName = "\\DNTUWRK\DATA\DIVISION\ITD\Backup database\Backup.mdb"
Here is where the record sets get created:
' Define the RecordSets
Set dbBackupRecord = OpenDatabase(DatabaseName)
Set qdfClusterServers = dbBackupRecord.QueryDefs("ActiveClusterServers")
Set qdfActiveServers = dbBackupRecord.QueryDefs("ActiveServers")
Set rsBackups =
dbBackupRecord.TableDefs("Activity").OpenRecordset(dbOpenDynaset)
Set rsServerList = qdfActiveServers.OpenRecordset(dbReadOnly)
Set rsClusterList = qdfClusterServers.OpenRecordset(dbReadOnly)
Set rsUBItable =
dbBackupRecord.TableDefs("UBI").OpenRecordset(dbOpenDynaset)
Set rsIncidentLog =
dbBackupRecord.TableDefs("IncidentLog").OpenRecordset(dbOpenDynaset)
With this process, the rsIncidentLog and rsbackups should be updating.
And here are two examples where the record sets should be updated:
Example 1:
With rsBackups
.AddNew
.Fields("UBI") = MyUBI
.Fields("Status") = MyStatus
.Fields("StartTime") = MyFileDate
.Fields("EndTime") = Null
.Fields("Kilobytes") = MyBytes
.Fields("State") = "Done"
.Fields("Type") = "Backup"
.Fields("JOBID") = "BackupEXEC"
.Fields("ActiveStart") = Null
.Update
End With
Example 2:
rsIncidentLog.AddNew
rsIncidentLog.Fields("IncidentTime") = Now()
rsIncidentLog.Fields("IncidentText") = "Backup - Complete Batch Process"
rsIncidentLog.Update
Example 1: I have never been able to get it working, I have added msgbox's
to print out the values and have stepped through the code in the debugger and
it does process these lines (i.e. it’s not a loop problem).
Example 2: Use to work but now it doesn’t.
Can anyone point me in the right direction as to why this doesn’t work?
Any help is always greatly appreciated.
Cheers,
GLT.
We initially had a process that ran in Excel VBA that checked our remote
servers and then updated an MS Access database.
I have now moved the VBA from Excel to MS Access.
So far the process seems to work OK, but none of the tables that should have
records added after process has run have any records.
I have stepped through the code (there are a lot of loops) and the record
updating (.addnew) does get processed.
I have also added message boxes to print out the values of the fields to be
updated and the values are fine.
Here is where all the DIM statement defined for the DB objects:
Dim dbBackupRecord As DAO.Database
Dim qdfActiveServers As DAO.QueryDef
Dim qdfClusterServers As DAO.QueryDef
Dim tbClusterCheck As DAO.QueryDef
Dim rsServerList As DAO.Recordset
Dim rsClusterList As DAO.Recordset
Dim rsBackups As DAO.Recordset
Dim rsUBItable As DAO.Recordset
Dim rsIncidentLog As DAO.Recordset
Dim DatabaseName As String
This is the current DB (this was orginally in the Excel code so I left it)
DatabaseName = "\\DNTUWRK\DATA\DIVISION\ITD\Backup database\Backup.mdb"
Here is where the record sets get created:
' Define the RecordSets
Set dbBackupRecord = OpenDatabase(DatabaseName)
Set qdfClusterServers = dbBackupRecord.QueryDefs("ActiveClusterServers")
Set qdfActiveServers = dbBackupRecord.QueryDefs("ActiveServers")
Set rsBackups =
dbBackupRecord.TableDefs("Activity").OpenRecordset(dbOpenDynaset)
Set rsServerList = qdfActiveServers.OpenRecordset(dbReadOnly)
Set rsClusterList = qdfClusterServers.OpenRecordset(dbReadOnly)
Set rsUBItable =
dbBackupRecord.TableDefs("UBI").OpenRecordset(dbOpenDynaset)
Set rsIncidentLog =
dbBackupRecord.TableDefs("IncidentLog").OpenRecordset(dbOpenDynaset)
With this process, the rsIncidentLog and rsbackups should be updating.
And here are two examples where the record sets should be updated:
Example 1:
With rsBackups
.AddNew
.Fields("UBI") = MyUBI
.Fields("Status") = MyStatus
.Fields("StartTime") = MyFileDate
.Fields("EndTime") = Null
.Fields("Kilobytes") = MyBytes
.Fields("State") = "Done"
.Fields("Type") = "Backup"
.Fields("JOBID") = "BackupEXEC"
.Fields("ActiveStart") = Null
.Update
End With
Example 2:
rsIncidentLog.AddNew
rsIncidentLog.Fields("IncidentTime") = Now()
rsIncidentLog.Fields("IncidentText") = "Backup - Complete Batch Process"
rsIncidentLog.Update
Example 1: I have never been able to get it working, I have added msgbox's
to print out the values and have stepped through the code in the debugger and
it does process these lines (i.e. it’s not a loop problem).
Example 2: Use to work but now it doesn’t.
Can anyone point me in the right direction as to why this doesn’t work?
Any help is always greatly appreciated.
Cheers,
GLT.