DoCmd.TransferDatabase : copy tables to another database without data
Copy MS-Access tables using VBA
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
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
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
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
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
Getting run-time 3009 error on "DoCmd.TransferDatabase acExport" statement
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.
Deletes Tables in Backend imports into Front End
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
Linked tables and copy
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.
Copy record from one table to another
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
Unique Record Identifier for MS SQL Server Tables
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
TransferDatabase Macro Action
You can use the TransferDatabase action to import or export data between the current Access database (.mdb or .accdb) or Access project (.adp) and another database. For Microsoft Office Access 2007 databases, you can also link a table to the current Access database from another database. With a linked table, you have access to the table's data while the table itself remains in the other database.
DeleteObject not working.
Has anyone else come across this problem in MS Access 2000?
I have an application that updates from an ODBC source overnight using a macro that is run from Scheduled Tasks. There are several tables that are first deleted using DeleteObject and then re-impoted using TransferDatabase. However the DeleteObject for the last table does not delete the tables. The TransferDatabase action then reimports the table but the suffix 1 is added
A2003: want to delete table but can't
A2003 on Win XP
Goal: Copy fresh data from production DB to my test DB. Code is in test DB.
- Delete table "oldTable1".
- Rename "Table1" to "oldTable1"
- Import "Table1" from production DB to test DB.
When I try to delete "oldTable1" I get an error about "oldTable1" being in a relationship and it cannot be deleted. How do I delete the relationship so I can delete the table?
Here is my code:
' Delete old tables first. DoCmd.DeleteObject acTable, "oldTable1" ' Rename tables to "old" prefix. DoCmd.Rename "oldTable1", acTable, "Table1" ' Import real tables from M: dbsrc = "M:\blah\production.mdb" tname = "Table1" DoCmd.TransferDatabase acImport, "Microsoft Access", dbsrc, acTable, tname, tname
I am trying to do the same thing as saving the Database using the Save As pull down with VBA code.
I have looked for examples on the web but nothing comes close.
Here's what I want to do:
Transfer all the tables (structure and data), form, reports, vba code everything to another accdr not accdb file on a local pc machine.
I want the user to pick the location on their local machine to save the file(I know how to do this).
Is this doable? Is so may I see example code using
FDialog Dynamic Table Seleting
I've come across a problem with importing access tables from one mdb to another in VB.
Usually I use FDialog and TransferDatabase.
The item im working on right now needs to import a table from 1 mdb (MDB1) to another (MDB2), but the table name in MDB1 changes daily.
Can anyone tell me how I can make this dynamic so that whatever the table name in MDB1 is, it will import.
(MDB1 only ever has one table).
This is the line of code I would normally use:
DoCmd.TransferDatabase acImport, "Microsoft Access", Me!txtPath, acTable, "TableName", "tbl_MyTable, DataOnly, False
Code to link text file as table
I'm trying to figure out how to link a text file to my database with VBA. I know how to do this for other types of data sources, using the DoCmd.TransferDatabase acLink command, but need guidance using this with TXT files
Copy Table Data Macro To Another Database
Is it possible to copy a table data macro from one data base to another?
I have a database that I use for development. Once happy with the development, I copy the changes over to a live database. With the backend (data) database I have developed a database that compares the development database to the live database, so I know what changes need to be made. This database also makes the changes to the live database.
With 2010 Access has introduced table based data macro's, which I want to start using. How can I copy these from the development database to the live database? At this stage I simply want to copy / import them. I will look at automating the process later
Copy tables from linked to local
I have an Access 2007 database with linked tables in a SQL 2005 database, and I want to create a local copy of the tables for testing purposes. It seems that this can be done in Access by copying the linked tables and pasting them with structure and data as local tables.
If the local tables are then modified in the Access database, will the original linked ones in the SQLdatabase be affected in any way?
Variables in TransferDatabase Action
I would like to be able to link a table from another database and then run some queries and stuff on it from a macro. I know that I will need to use the TransferDatabase action (with the Link type). The only issue is that I would like to be able to specify the to the database to link to somewhere other than in the macro, such as in a field on a Form. Is this possible? How do I go about doing it (adding [Forms!FormName!FieldName!] didn't work).
How to remove a linked table
I link a table to my database. But how to remove such links from the database with VBA?