473,583 Members | 3,382 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Copying an entire table using vba

94 New Member
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 10438
ADezii
8,834 Recognized Expert Expert
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
James Bowyer
94 New Member
Thanks, I'll try this and let you know how I get on.
Nov 16 '10 #3
James Bowyer
94 New Member
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,566 Recognized Expert Moderator MVP
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
James Bowyer
94 New Member
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,566 Recognized Expert Moderator MVP
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?
Nov 18 '10 #7
James Bowyer
94 New Member
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,834 Recognized Expert Expert
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
James Bowyer
94 New Member
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

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

Similar topics

3
2663
by: Jason | last post by:
In enterprise manager I am copying a table from one database to another. I am using the dts wizard to import the data. After I successfully import the data, I open both tables to compare the records to make sure they are the same. I right click on a field and click "last" for both tables. However, the record is different for both. If I do...
1
11000
by: sqlnewbie | last post by:
I'm a newbie to script writing. I'm trying to write a script to copy all data from a table to the same table in a 2nd database. Both databases are on the same server and are identical in design. I can do this with DTS but wanted a script I could email to a user to run in Query Analyzer. Example: Copy entire table called 'Customers' in...
5
2949
by: Robin Tucker | last post by:
Hi, I need to lock a table so that Inserts are prevented as well as deleted and updates. At present I'm thinking this might do it: SELECT * FROM myTable WITH(UPLOCK) but then again I'm not sure whether this will cover the insert case.
4
3927
by: Nathan Sokalski | last post by:
When editing an ASP Table, Visual Studio does not allow me to edit it in Design View. This makes it harder to add elements, because I must add every element either by using Design View to create the element outside of the table and then using cut & paste in HTML View to move it to the desired location, or by manually typing the code in using...
22
18782
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4. The system is client/server, multiusers based. The MDBs are using record locking. Here is part of the code: Dim wkSpace As Workspace, db As...
2
2124
by: DanLezoche | last post by:
i have a client who's database requires users to enter the beginning date and ending date for contracts. i would like to create a vba function that checks the end date w/ the now() function (from what i've gathered) and updates the status field with "Expired" if the end date has passed. can i have this function run when the form opens,...
6
3522
by: AshleyB | last post by:
2 phpbb boards I run have been hacked. One I've restored but the other one has had some damage to the MySQL db. A table is missing. As it's a styles table I thought I could could copy a table, from a test board I set up, to the hacked board. I tried this using phpMyAdmin and I get this error: SQL-query: Edit CREATE TABLE...
5
4445
by: thatcollegeguy | last post by:
Below are my 3php and 2js files. I create a table using ajax/php and then want to change the values in the tables add(+ number for teamid) id's for each specific td in the table. I don't know if I have the entirely wrong approach, but my code is below. Any or all help is appreciated! What currently happens is that the getdivision.php...
20
3392
by: omar999 | last post by:
is it possible to perform a cast function on an entire table via sql? im basically trying to select all table contents multiply by 1.1 and then display the converted data on a webpage using sql, classic asp. i can do a cast function on a single table column like this Select columnname = Cast(columnname * 1.1 as int)from tablename WHERE ID...
0
1430
by: Tim Mullin | last post by:
Hi all, This is a request that seems pretty common, but I have yet to find a definitive answer for it. I'm also fairly new to Access/VBA, so my apologies if this seems redundant. I would like to create a simple text box on a form accompanied by a button that runs a command/query/macro that will search an entire table for a keyword that a...
0
7894
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8172
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8320
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7929
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6577
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5697
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5370
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3814
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3841
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.