Topics Search

DoCmd.TransferDatabase : copy tables to another database without data

DoCmd.TransferDatabase : copy tables to another database without data
Views: 253
I need to copy my tables into another database but without the data. I am using:

DoCmd.TransferDatabase acExport, "Microsoft Access", laptopDBSource, acTable, _ tblSourceDest, tblSourceDest, True

It is copying but it copies the data of the table even though I am leaving the structure as TRUE! (btw, I have tried with False as well and it copied the data the same way!)
Could it be because my tables are linked and not direct in my front end?
Sponsored Links:

Similar posts...


Copy MS-Access tables using VBA

Copy MS-Access tables using VBA Icon
Need to know how (and if it's possible) to copy data from db2 to db3 using VBA code in db1? I know how to copy tables from current database to another (using transferdatabase), but am required to distribute this data to other database in a procedure.

TransferDatabase and run-time error 3024

TransferDatabase and run-time error 3024 Icon
I am creating an archive method for a database that has grown too large. I have queries that generate the data I need to archive, and I have created code that creates a new database that I want to export the archive data to. I then use DoCmd.TransferDatabase to send the results of the queries to the new database.
I am getting a run-time error 3024 which tells me the destination database cannot be found. Yet it does exist, in precisely the same path and name as it says it cannot find.
I know nothing is misspelled as the path name used to create the new database is passed to the routine that uses transferdatabase.
Does anyone know if this is a bug and how to get around it?

Access2007 - writing to an .accdb, not an .mdb

Private Sub ArchiveTables(ArcDB As String)
DoCmd.TransferDatabase acExport, "Microsoft Access", ArcDB, acTable, "ArchiveBasicDataQuery", "Basic_Data

Problem with TransferDatabase function

Problem with TransferDatabase function Icon
I have an Access 2007 specifically for report-building using link tables. There's very little data in local tables, except for small tables used to filter reports, populate list boxes, etc. Right now, I'm trying to make a local copy of this db, including the reports, queries, forms, etc.

from the link table db, but with local tables for opening reports on computers where theODBC connection isn't available.

I've set it up to be able to make a copy of the link table db, then use the TransferDatabase to bring in local tables to replace the link tables. At first, the copies were all showing up as firsttable1, secondtable1, etc., which still left my reports to run from the link tables.

I modified the code to delete the link table before making the copy...

DoCmd.CopyObject Not working

DoCmd.CopyObject Not working Icon
I am trying to copy a table (structure and data) from my application to another database using the DoCmd.CopyObject method, but it is not working. Here is my code:

DoCmd.CopyObject "C:\iHaunt\Resources\Update.accdb", "tblParameters", acTable, "tblParameters"
DoCmd.OpenForm "frmLogin1", acNormal
DoCmd.Close acForm, Me.Name

I do not get an error when this code runs and it opens the form "frmLogin1" and closes the current form just fine. But when I go to the other database, the table is not there. Does the other database need to be open?

I've also tried this with DoCmd.TransferDatabase, and that does not work either

Use VB to mMake a local copy of a Sharepoint linked table

Use VB to mMake a local copy of a Sharepoint linked table Icon
I'm able to do a local copy manually, of course, but I have a need to copy the table structure locally. Using DoCmd.TransferDatabase results in creating a copy on the Sharepoint server

Deletes Tables in Backend imports into Front End

Deletes Tables in Backend imports into Front End Icon
I posted this before with no resolve, hoping this time around with better results.

I have a function in the back end of my database that deletes and imports tables from MAS90, I can not link to these tables so the best resolve is nightly the tables are deleted and re-imported, works fine.

BUT, there is an occasion to force this to happen and I call the function from a form in the front end of the split database. It deletes the tables in the backend like it should but then proceeds to import them into the front end.

Note*In the front end I went to Tools, References and found the back end database in order to call the function but why the glitch?

I have been trying to find with the TransferDatabase for the destination is there a way to designate the backend of the database is where the tables should be imported to?

DoCmd.TransferDatabase acImport, "ODBC", "C:\WINDOWS\DESKTOP;CODEPAGE=1252;;TABLE=JC2_JobCo stDetail", acTable, "JC2_JobCostDetail", "JC2_JobCostDetail", False

Getting run-time 3009 error on "DoCmd.TransferDatabase acExport" statement

Getting run-time 3009 error on "DoCmd.TransferDatabase acExport" statement Icon
I am using MS2007 on windows XP. I support an access application which has a 'main menu' (form) and on this form there is a button 'Exit Database'. In the 'on click' event code for the'Exit Database' button the following is code is executed to copy a table to another database:

1) DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Inv\ITH.mdb", acTable, "HWI", "HWI"
2) This produces the run-time 3009 error which states "You tried to lock table "HWI" while opening it, but the table cannot be locked because it is currently in use."

There is no code on this 'form_load' event on this form that references this table, there is no recordsource defined on the data property for this form. I see no where that this table isreferenced or open at this point.

Copy record from one table to another

Copy record from one table to another Icon
Attached is a simple database that I created to try to explain what I am attempting to accomplish with my application database. In essence, I would like to copy the data from one form/subform and its associated tables to another form and its associated tables. I know the first question will be, why do I want to copy data from one table to another? The answer is, the data for both forms reside in different databases which are independent tools for different parts of our business. We would like to "borrow" data from the one database into the other.

In my example, I put all the tables in one database and labeled one set as LINKED to simulate the linked tables to the second database. I have a button that when clicked, should copy the data from the one form and populate it to the other form where the user can edit if desired.

I appreciate any help that anybody can lend to get me started with the code for this button. Please see the attached database

Linked tables and copy

Linked tables and copy Icon
I was just asked to make a copy of the Access database we are using. It is made by someone else then me, I just need to create an (empty) copy of this database.

What I have is a database cms.mdb that is full of linked tables. These linked tables link to another database cms_data.mdb, which contains all the data.

Now I want a copy of the cms database so I can clear all the data of cms_data_new.mdb to show to a new customer.

I copied all the files to a new location, but the linked tables in the cms_new.mdb database still link to the original database.

My question is now: How can I edit these links or what would be the best solution to make a copy of the database? I hope I am clear enough on this one.

Unique Record Identifier for MS SQL Server Tables

Unique Record Identifier for MS SQL Server Tables Icon
I have been using DoCmd.TransferDatabase. to link to an SQL Server. Usually what I do is delete the table first and then use this command. Most of the tables have a Primary Key and it works fine. However, there are some tables that do not have a Primary Key designated and I get the "Unique Record Indentifer" pop up window, click OK and it continues on. I would like to know if there is a way around having this window come up? Is there something that I can add to DoCmd.TranferDatabase line to automatically do this for me or another command. Locally I do not have access to change anything on the SQL Server Database