469,271 Members | 1,785 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Copying an entire table using vba

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!
Nov 16 '10 #1
21 9689
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_DblClick(Cancel As Integer)
  2. On Error GoTo Err_Detail_DblClick
  3. 'The following Code will copy the Employees Table in the Current Database to the
  4. 'Employees_Old Database in the C:\Archives\ Directory, it will be renamed as
  5. 'Employees_Copy
  6.  
  7. '******************************** USER DEFINED ********************************
  8. Const conDESTINATION_DATABASE As String = "C:\Archives\Employees_Old.mdb"
  9. Const conDESTINATION_TABLE_NAME As String = "Employees_Copy"
  10. Const conSOURCE_TABLE_NAME As String = "Employees"
  11. '******************************************************************************
  12.  
  13. DoCmd.CopyObject conDESTINATION_DATABASE, conDESTINATION_TABLE_NAME, acTable, conSOURCE_TABLE_NAME
  14.  
  15. MsgBox "The Table [" & conSOURCE_TABLE_NAME & "] has successfully been copied to " & _
  16.         conDESTINATION_DATABASE & " as " & conDESTINATION_TABLE_NAME & ".", _
  17.         vbInformation, "Table Copy Operation Completed"
  18.  
  19. Exit_Detail_DblClick:
  20.   Exit Sub
  21.  
  22. Err_Detail_DblClick:
  23.   MsgBox Err.Description, vbExclamation, "Error in Table Copy Operation"
  24.   Resume Exit_Detail_DblClick
  25. End Sub
Nov 16 '10 #2
Thanks, I'll try this and let you know how I get on.
Nov 16 '10 #3
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?
Nov 18 '10 #4
NeoPa
32,171 Expert Mod 16PB
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.
Nov 18 '10 #5
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.
Nov 18 '10 #6
NeoPa
32,171 Expert Mod 16PB
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?
Nov 18 '10 #7
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.
Nov 18 '10 #8
ADezii
8,800 Expert 8TB
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!
Nov 18 '10 #9
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?
Nov 18 '10 #10
NeoPa
32,171 Expert Mod 16PB
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.
Nov 18 '10 #11
apologies for my ignorance, but how would I do it using an append query?
Nov 18 '10 #12
NeoPa
32,171 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [tblDest]
  2. SELECT      *
  3. 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.
Nov 18 '10 #13
Steven Kogan
107 Expert 100+
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.
Nov 18 '10 #14
NeoPa
32,171 Expert Mod 16PB
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.
Nov 18 '10 #15
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...
Nov 18 '10 #16
NeoPa
32,171 Expert Mod 16PB
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?
Nov 18 '10 #17
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2.  
To stop it warning me about overwriting the data?
Nov 18 '10 #18
NeoPa
32,171 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM   [tblDest]
Nov 18 '10 #19
ADezii
8,800 Expert 8TB
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.
Attached Files
File Type: zip Backup.zip (55.6 KB, 150 views)
Nov 18 '10 #20
NeoPa
32,171 Expert Mod 16PB
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).
Nov 18 '10 #21
ADezii
8,800 Expert 8TB
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.
Nov 18 '10 #22

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by sqlnewbie | last post: by
5 posts views Thread by Robin Tucker | last post: by
4 posts views Thread by Nathan Sokalski | last post: by
2 posts views Thread by DanLezoche | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.