UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Send Table To Another Database, Access 2016    
 
   
River59
post Dec 4 2017, 03:39 PM
Post#1



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


I have a database that makes some changes to records on a table, copies records from that table to a new table, naming it for the time period (Year/Qtr) selected. This table is saved in the database ... yes, it is writing the same data over to a new table every quarter. Then further changes are made to these records on the original table ... status, values, etc.

I just want to send these quarterly tables to an 'historical' database after this is complete to get them out of the way, but still have them available for future reference. It can be a simple copy this table from this database, paste it into this other database, now delete it from this one.

Easiest way to do this using VBA?

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
theDBguy
post Dec 4 2017, 03:54 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You might try looking into the TransferDatabase method?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
River59
post Dec 4 2017, 03:59 PM
Post#3



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


Looking into it now. May be what I need but it will have to wait until morning.
Thanks for looking dbGuy.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
theDBguy
post Dec 4 2017, 04:05 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


If you use it, you might want to make sure you add an error handler because things could go wrong when exporting objects over the network.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
River59
post Dec 5 2017, 08:46 AM
Post#5



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


I'm not getting something right here:

CODE
Dim HistDb As String
HistDb = "GM Global Tradeflows_Historical.accdb"
Debug.Print CurrentProject.Path & "\" & HistDb

DoCmd.TransferDatabase , acExport, CurrentProject.Path & "\" & HistDb, acTable, "tbl_UTPs (2017 Q2)"

The Debug.Print CurrentProject.Path & "\" & HistDb line produces this (which is what I want).

C:\Users\xzbqxh\Documents\Kim\GM Global Tradeflows_Historical.accdb

tbl_UTPs (2017 Q2)
is the table that I want to send to the Historical database.

When I try to run I get this error:
Attached File  CopyError.PNG ( 15.26K )Number of downloads: 0


Any ideas where I took a wrong turn?

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
River59
post Dec 5 2017, 09:18 AM
Post#6



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


Here is what I found to do this:

DoCmd.TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)

TransferType, acExport
DatabaseType, Default
DatabaseName, What database to copy to – Current Project.Path & db Name
ObjectType, acTable
Source, Name of table to copy - "tbl_UTPs (2017 Q2)"
Destination, Name of table in the database copying to - "tbl_UTPs (2017 Q2)"
StructureOnly,
StoreLogin

This is my command now:

CODE
DoCmd.TransferDatabase , acExport, , CurrentProject.Path & "\" & HistDb, acTable, "tbl_UTPs (2017 Q2)", "tbl_UTPs (2017 Q2)"


I get a type mismatch error.


--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
theDBguy
post Dec 5 2017, 10:51 AM
Post#7


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Try taking out the first comma after TransferDatabase.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
River59
post Dec 5 2017, 11:27 AM
Post#8



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


dbguy, when I do that, it goes back to the original error message 'The 1 type isn't an installed ....'.
My understanding is that comma defaults the Database type part of the code as an Access database.

I have also checked the references and they are the same in both databases. It just can't possibly be this darn hard ... confused.gif

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
theDBguy
post Dec 5 2017, 11:50 AM
Post#9


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Here's a code I just used to transfer a table from a test database into a demo database:

CODE
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentProject.Path & "\demo.accdb", acTable, "archive", "newarchive"

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
River59
post Dec 5 2017, 12:02 PM
Post#10



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


You have got to be kidding me ... thank you dbGuy.
The outpoint was believing that the second comma told it to default to Access, the only change I made in my code was to precede it with "Microsoft Access" as you had done. Works great now.

CODE
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentProject.Path & "\GM Global Tradeflows_Historical.accdb", acTable, "tbl_UTPs_2017 Q2", "tbl_UTPs_2017 Q2"


Thank you so much for the help!

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
theDBguy
post Dec 5 2017, 12:09 PM
Post#11


Access Wiki and Forums Moderator
Posts: 71,233
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You're welcome. Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 06:29 PM