Hi Frank,
I do not believe Compact and Repair is causing your problem.
I am not sure I understand everything correctly but I think you are saying
you are importing a table from a development database to your "live" database.
But first you need to delete the table in the in the live database.
If you have the Front End Database open and you delete a table, you are not
deleting the actual table but only the link to the table. To delete the
table you need to open the back end and delete the table there. Then you can
import your updated table.
You can also delete the table using code from the Front End or your
Development database:
'-----------------------------------------------------------------------------
----------
' Procedure : DelRemoteTbl
' Purpose : Delete table in another database
' Arguments : strTableName--The name of the Table you want to drop
' : strDbPath--The full path and filename of the remote Database
' : that contains the table to drop
' : The Table's Relationships should be deleted prior to using this
procedure.
' Example : Call DelRemoteTbl("tblMyTable", "C:\Databases\MyDatabase")
'-----------------------------------------------------------------------------
----------
'
Public Sub DelRemoteTbl(strTableName As String, strDbPath As String)
On Error GoTo Err_DelRemoteTbl
Dim db As DAO.Database
Dim strTest As String
Set db = DBEngine(0).OpenDatabase(strDbPath)
' Check to see if the table exists
strTest = db.TableDefs(strTableName).Name
' If no error, then delete the table
If Err = 0 Then
db.TableDefs.Delete strTableName
MsgBox strTableName & " was dropped from " & strDbPath
End If
Exit_DelRemoteTbl:
' Release memory
Set db = Nothing
Exit Sub
Err_DelRemoteTbl:
MsgBox strTableName & " was NOT DELETED in " & strDbPath _
, vbCritical, " " & _
" Table Not Deleted"
Resume Exit_DelRemoteTbl
End Sub
If you use this code, you will need to delete the table link in the Front End.
If you do this often it would be better to update using code so you do not
have to delete and import tables. The following code is an example. You
would need to change the field names to match your table:
'-----------------------------------------------------------------------------
----------
' Procedure : AppendRemoteTbl
' Purpose : Append records not found in a Remote Database
' Arguments : strDbPath--The full path and filename of the remote Database
' Example : Call AppendRemoteTbl("C:\Databases\MyDatabase")
'-----------------------------------------------------------------------------
----------
'
Public Sub AppendRemoteTbl(strDbPath As String)
On Error GoTo Err_AppendRemoteTbl
Dim db As DAO.Database ' This is your Development database
Dim dbRemote As DAO.Database ' Your live database
Dim rst As DAO.Recordset
Dim rstRemote As DAO.Recordset
Dim lngID As Long
Dim strFind As String
Set dbRemote = DBEngine(0).OpenDatabase(strDbPath)
Set db = CurrentDb
Set rstRemote = dbRemote.OpenRecordset("tblPeople", dbOpenDynaset)
Set rst = db.OpenRecordset("tblPeople", dbOpenDynaset)
With rst
Do While .EOF = False
lngID = !PersonID
strFind = "PersonID = " & lngID
rstRemote.FindFirst strFind
' If a match is not found add the record to the remote table
If rstRemote.NoMatch Then
rstRemote.AddNew
rstRemote!FirstName = !FirstName
rstRemote!MiddleName = !MiddleName
rstRemote!LastName = !LastName
rstRemote!Suffix = !Suffix
rstRemote.Update
End If
.MoveNext
Loop
End With
Exit_AppendRemoteTbl:
' Release memory
rst.Close
rstRemote.Close
Set rst = Nothing
Set rstRemote = Nothing
Set dbRemote = Nothing
Set db = Nothing
Exit Sub
Err_AppendRemoteTbl:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & _
" In procedure AppendRemoteTbl"
Resume Exit_AppendRemoteTbl
End Sub
This is not beginner's code so if you have any questions or need help be sure
to let us know.
Best Regards,
Patrick Wood
http://gainingaccess.net
