What is done when we compact and repair

F

Frank Situmorang

Hello,

I do development of my church membership and then for forms table contents
and queiries can be imported to the other one the go live database.

The way I do it for table, since this is language table, as there is
addition of words, in the live database, I delete the table say FormLabels,
then I import the table from the developped database.

But I do not see in the live database the additional words. My question is
is it because I do compact and repair in the live database cancelled the
imported table?. For the form and Query it is OK, but not for the table.

If we delete the table should we do it in the BackEnd?, or we can do it in
the Front End.

Thanks for any comments

Frank
 
H

Hunter57 via AccessMonster.com

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
 
P

Piet Linden

Hello,

I do development of my church membership and then for forms table contents
and queiries can be imported to the other one the go live database.

The way I do it for table, since this is language table, as there is
addition of words, in the live database, I delete the table say FormLabels,
then I import the table from the developped database.

But I do not see in the live database the additional words. My question is
is it because I do compact and repair in the live database cancelled the
imported table?. For the form and Query it is OK, but not for the table.

If we delete the table should we do it in the BackEnd?, or we can do it in
the Front End.

Thanks for any comments

Frank

Why not just delete the contents of the table your are importing into
and then just import the contents of the table in the other DB? Then
you don't need to delete anything or mess up the relationships.
 
J

JimBurke via AccessMonster.com

It sounds to me like all of your users would want access to the exact same
data for something like word definitions. Is that the case? If so, the table
should be in the front end. There's no need to put a table in a back end
unless the data will potentially be different from user to user. Any time I
have a table that I know should be the same for all users I put it in the
front end. Maybe I don't quite understand how this table is used.
 
H

Hunter57 via AccessMonster.com

Hi Frank,

I am trying to write so you can understand the meaning of my words.

We need you to give us more information so we can know how to give you the
help you need.

What is the purpose of the table you want to update. Are you adding many new
records (rows) to the table or just a few.

Is this database being used on a network by more than one person at the same
time?

Are you the only one that edits or adds data to the table or do others make
edits or additions also?
 
J

John W. Vinson

It sounds to me like all of your users would want access to the exact same
data for something like word definitions. Is that the case? If so, the table
should be in the front end. There's no need to put a table in a back end
unless the data will potentially be different from user to user. Any time I
have a table that I know should be the same for all users I put it in the
front end. Maybe I don't quite understand how this table is used.

Jim, I think you have "front" and "back" reversed here! Is that what you
meant? The frontend is the individual copy on each user's desktop; the backend
is the single shared data.
 
F

Frank Situmorang

Thanks to all of your ideas are greatful.

Frank

Hunter57 via AccessMonster.com said:
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
 
K

Keith Wilby

JimBurke via AccessMonster.com said:
It sounds to me like all of your users would want access to the exact same
data for something like word definitions. Is that the case? If so, the
table
should be in the front end. There's no need to put a table in a back end
unless the data will potentially be different from user to user. Any time
I
have a table that I know should be the same for all users I put it in the
front end. Maybe I don't quite understand how this table is used.

But if the C drive gets trashed or the user uses a different PC then you
have a problem. No "valuable" data should be stored in the FE file. :)

Keith.
www.keithwilby.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top