Copying an entire table using vba

[James Bowyer]

I realise this is a bit of a strange request, but I need to copy a few tables using vba, the reason being one of our remote sites has a VPN connection that is unbearably slow, and when I tried to host the back end at our main site it was so slow that no-one would use the program.

So, what I want to do is have tables that are an exact copy of those that are hosted at the main site, so that whilst using the program they are the tables referenced, and then on exiting, it will update the core database.

Obviously, I realise that this will only be able to be done one way (unless we can be really clever), and therefore anyone from the main site wanting to view this part of the database would be read only, but this should be fine.

Unless anyone can suggest a better way of updating a core database without a constant connection?

Thanks!

[ADezii]

You can try something along these lines, substituting your own Table Names as well as Destination Database. If the Table you are copying already exists in the Destination Database (renamed), you will then receive a Prompt asking if you wish to Overwrite it.

Private Sub Detail_DblClick(Cancel As Integer)
On Error GoTo Err_Detail_DblClick
'The following Code will copy the Employees Table in the Current Database to the
'Employees_Old Database in the C:\Archives\ Directory, it will be renamed as
'Employees_Copy
 
'******************************** USER DEFINED ********************************
Const conDESTINATION_DATABASE As String = "C:\Archives\Employees_Old.mdb"
Const conDESTINATION_TABLE_NAME As String = "Employees_Copy"
Const conSOURCE_TABLE_NAME As String = "Employees"
'******************************************************************************
 
DoCmd.CopyObject conDESTINATION_DATABASE, conDESTINATION_TABLE_NAME, acTable, conSOURCE_TABLE_NAME
 
MsgBox "The Table [" & conSOURCE_TABLE_NAME & "] has successfully been copied to " & _
        conDESTINATION_DATABASE & " as " & conDESTINATION_TABLE_NAME & ".", _
        vbInformation, "Table Copy Operation Completed"
 
Exit_Detail_DblClick:
  Exit Sub
 
Err_Detail_DblClick:
  MsgBox Err.Description, vbExclamation, "Error in Table Copy Operation"
  Resume Exit_Detail_DblClick
End Sub

[James Bowyer]

Thanks, I'll try this and let you know how I get on.

[James Bowyer]

I got the error "cannot delete tblfitters, its is participating in one or more relationships".

I added code to close the form first, so that nothing was open, and still got this error. Any ideas why?

[NeoPa]

A perrenial problem, and not one with an easy solution. In a similar situation I would look very hard at reducing the requirement. Multi-user updating of central data without a direct link to that data involves all sorts of technical difficulties (one-way is complex enough, but both ways is much more so).

If I were convinced of the absolute necessity then I'd look at time-logged deltas. It doesn't guarantee that any change is not overwritten by somebody else's, but it can be used to ensure that only the last change is applied. The deltas would be in the forms of additions, amendments and deletions, and obviously would include a date/time stamp.

The master, and all satelites, would need to process all of these deltas whenever they were found. The deltas can be stored in a table which is linked across the VPN, as this should not materially effect the general running of the database, but only that process where the data is kept synchronised.

I would also suggest, to be sure of the data, a re-copy from the master every day.

[James Bowyer]

Basically, the point of it is to backup the data, if the users computer goes down, we lose all the data, however, if its all automatically backed up on our server, then we don't lose everything. Obviously losing the front end isn't a problem, as I created it and therefore have a copy.

[NeoPa]

James Bowyer:
So, what I want to do is have tables that are an exact copy of those that are hosted at the main site, so that whilst using the program they are the tables referenced, and then on exiting, it will update the core database.

That seems to be a contradiction. What am I misunderstanding?

[James Bowyer]

Apologies, I "might" ;) have got it the wrong way round in the first case. The database will be used at the remote site by 1 user, but I want it backed up at the main site, and preferably into the back end of the main database we use for everything else, so its easy to reference.

[ADezii]

The Error Message you describe in Post# 4 happens when you attempt to Delete a Table that is involved in 1 or more Relationships with other Tables. Access will not allow you to Delete the Table without first Deleting the Relationship(s) to other Tables, after which you may proceed with the Deletion. I am a little confused as to why you would want to Delete this Table, anyway. Kindly explain.

P.S. - Be extremely careful before Deleting any Relationships among Tables!

[James Bowyer]

I wasn't trying to delete anything! I am guessing it has something to do with the copying to another database that doesn't have the same relationships?

[NeoPa]

No worries. In that case a simpler append query (after the destination table is cleared down) would do you, I would have thought. Use the slow link for that, and when it's done the database can work at full speed again.

[James Bowyer]

apologies for my ignorance, but how would I do it using an append query?

[NeoPa]

None required James.

If you have a linked table to the master table, and a separate table for the local copy, then you would create an append query either in raw SQL, or more easily still, using the New Query wizard and selecting Design View or Simple Query Wizard. In the latter case select design view when finished. In design view change the type to an Append query and select the destination table required.

For Raw SQL use something like :

INSERT INTO [tblDest]
SELECT      *
FROM        [tblSource]

Clearly, this is only the append side. The clearing of previous contents is still required before this stage, to ensure an accurate copy.

[Steven Kogan]

Access databases work well on a good LAN, but once you have users connecting from outside your LAN the database speed become unbearable, as you have found. Essentially, MS Access does not work well on a WAN - Wide Area Network.

If you have users spread out geographically, or even if you have users connecting from outside of the building where your server is, you may want to consider using Remote Desktop Services or Citrix.

If you are lucky your company may already have Remote Desktop Services or Citrix already available. Remote Desktop Services used to be called Terminal Services.

When you are using MS Access on Remote Desktop Services it is like you are working locally, so your application typically would not need to be modified.

[NeoPa]

I would agree with Steven that RDC (Remote Desktop Connection) would be well worth considering, to avoid even the need for this question. I must admit it's pretty well what I set up for my users when it became clear they would have to access the databases from somewhere where the connection was just too slow (Access is particularly unforgiving in this area). It's not so much an answer as an alternative solution.

If this fits your needs, I'd certainly recommend it over the more fiddly table-copying approach.

[James Bowyer]

We do already use terminal services for another database based in another office, however, the speed of connection is not great from our office, let alone the remote site that we are trying to run this from!
Its a nice option, but unfortunately not suitable in this case.
I might simply have to get the user to email me the back end every so often...

[NeoPa]

James Bowyer:
I might simply have to get the user to email me the back end every so often...

That last post was only an alternative approach. I thought I'd already answered your question about how to transfer the data. Is there anything you're still unclear on?

[James Bowyer]

Beg your pardon, Missed that post!
I'll give that one a try and see how we get on.
How would I clear the existing contents?

Or can I simply put

DoCmd.SetWarnings False
 

To stop it warning me about overwriting the data?

[NeoPa]

James Bowyer:
Or can I simply put

No. You really couldn't do that. It would not remove data that was no longer valid, nor would it update data that clashed. You would have to do as I suggested and clear the data first.

To clear a table in SQL simply execute :

DELETE
FROM   [tblDest]

[ADezii]

James, I am Attaching what I feel is an excellent Utility that may/may not be of use to you in your specific situation. I retrieved it from the Access Cookbook, and made some slight modifications to it. It allows you to Back Up Selected Objects in the Current Database to an External Database. It accomplishes this via a MultiSelect Listbox and what is referred to as a Callback Function. The Code is a little complex, but is completely encapsulated in a Form and Class Module, so no real knowledge of programming is required. Simply:

1. Import frmBackup and the Info Class Module into your Database.
2. Open frmBackup.
3. Select any Number of Objects to Backup to an External Database which has been previously defined.
4. Click the Backup Command Button.
5. All Selected Objects will be backed up to an External Database contained within a BACKUPS Folder under the Folder that contains the Current DB.
6. Any questions, feel free to ask.

P.S. - This is to viewed as only a Temporary Solution until you find a more practical one, as previously indicated by NeoPa.

[NeoPa]

If you're thinking of post #5 and the time-logged delatas ADezii, then I should clarify that is no longer seen as required. There was a miscommunication which has since been cleared up, so nothing of that complexity is now required (thank goodness).

[ADezii]

Thanks NeoPa, just another misread by me! (LOL). If you get a chance, download the Attachment that I made available in Post #20, I think that it is a really handy Utility.