transfer table programmatically

Posted on 2012-03-28
Last Modified: 2012-03-28
I need to send a table that is sent to us daily to an archive when we are through with it.
The name of the table is something like PRWBBEPI_****. The last 4 digits will be different. Can i use a wild card in the function and if so, how? Thanks for any help

Public Function Send2Archive()

 DoCmd.TransferDatabase transfertype:=acExport, _
  databasetype:="Microsoft Access", _
  databasename:="C:\Documents and Settings\Leidago\My Documents\destination.mdb", _
  ObjectType:=acTable, Source:="users", _
  Destination:="userstbl", structureonly:=False
End Function
Question by:Serena2345
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 37778035
do you have more than one table that starts with "PRWBBEPI_" ?

dim td as dao.tabledef, db as dao.database, sTable as string
set db=currentdb

for each td in db.tabledefs
        if left(,9)="PRWBBEPI_" then

            msgbox sTable

            Send2Archive stable

you can change your function to accept variable names

Public Function Send2Archive(strTable as string)

 DoCmd.TransferDatabase transfertype:=acExport, _
  databasetype:="Microsoft Access", _
  databasename:="C:\Documents and Settings\Leidago\My Documents\destination.mdb", _
  ObjectType:=acTable, Source:=strTable, _
  Destination:=strTable, structureonly:=False
End Function

Author Comment

ID: 37778183
There is a table called PRWBBEPI" but no "_" so your suggestion should work. As always Cappie Thanks

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now