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 misunderstandin g?

[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.