C
Cameron
I am working on cleaning up a database that someone else build and that has
had it's data curupted. I needed to build some new tables from filtered data
of the old tables so I thought using a select into query would work the best
for this situation.
But I keep getting a error saying that the tables can not write to the
database because they are linked. Is there a way programically to remove the
links and then re-establish them after the query has run to build the tables?
The query I am using is as follows:
Function BuildNew() ' Filters EmpSessions table and builds new session
tables from the filtered data
Dim db As Database
Dim rst As Recordset
Dim sSql As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("SessionType")
rst.MoveFirst
Do Until rst.EOF ' look through records
sSql = "SELECT EmpSessions.EmpID, EmpSessions.CourseID,
EmpSessions.SessionID, EmpSessions.CertificationDate, EmpSessions.ExpiryDate,
EmpSessions.Followup INTO " & rst("CatTable")
sSql = sSql & " FROM (TrainingTypeLookup INNER JOIN CoursesMaster ON
TrainingTypeLookup.TrainingType = CoursesMaster.TrainingType) INNER JOIN
EmpSessions ON CoursesMaster.CourseID = EmpSessions.CourseID"
sSql = sSql & " WHERE (CoursesMaster.Active = True) AND
(((TrainingTypeLookup.TrainingType)= '" & rst("Catagory") & " '));"
'db.Execute sSql ' builds new tables
DoCmd.RunSQL sSql
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function
had it's data curupted. I needed to build some new tables from filtered data
of the old tables so I thought using a select into query would work the best
for this situation.
But I keep getting a error saying that the tables can not write to the
database because they are linked. Is there a way programically to remove the
links and then re-establish them after the query has run to build the tables?
The query I am using is as follows:
Function BuildNew() ' Filters EmpSessions table and builds new session
tables from the filtered data
Dim db As Database
Dim rst As Recordset
Dim sSql As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("SessionType")
rst.MoveFirst
Do Until rst.EOF ' look through records
sSql = "SELECT EmpSessions.EmpID, EmpSessions.CourseID,
EmpSessions.SessionID, EmpSessions.CertificationDate, EmpSessions.ExpiryDate,
EmpSessions.Followup INTO " & rst("CatTable")
sSql = sSql & " FROM (TrainingTypeLookup INNER JOIN CoursesMaster ON
TrainingTypeLookup.TrainingType = CoursesMaster.TrainingType) INNER JOIN
EmpSessions ON CoursesMaster.CourseID = EmpSessions.CourseID"
sSql = sSql & " WHERE (CoursesMaster.Active = True) AND
(((TrainingTypeLookup.TrainingType)= '" & rst("Catagory") & " '));"
'db.Execute sSql ' builds new tables
DoCmd.RunSQL sSql
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function