Using wildcard as source and destination name in TransferDatabase??

[Emmie1]

Hello,
I have a database that has about 60 queries in it. A new database will be created each month. Using a command button, I would like to write code to import all of the queries from the previous months database into the current months database for the user. The code I have written so far will prompt the user for the current month, based on their answer I have several variables setup with the directory and name of the database.

Is there a way to use transferdatabase with wildcards in order pickup ALL queries from another database?

Here's the code that I have so far:

CURRENTMONTHNUM = InputBox("Please enter Current Month number (ex: if month is January enter '01) :", "Current Month", "##")
Select Case CURRENTMONTHNUM
Case Is = "06"
CURRENTMONTH = "June"
prevMonth = "X:\Month\May\May.mdb"
Directory = "X:\Save\"
NewMonth = "X:\NewMonth\June\"
TextImport = "May.txt"

DoCmd.TransferText acImportDelim, "Activity Import Specification", "New Activity", Directory & TextImport

DoCmd.TransferDatabase acImport, "Microsoft Access", prevMonth, acQuery, 01query, 01query, False

DoCmd.TransferDatabase acImport, "Microsoft Access", prevMonth, acQuery, 02query, 02query , False

----- I would like to use a wildcard for the source and destination for the query so that it will pickup ALL queries so I will not have to write 60 DoCmd lines. - Does anyone know if there is a way to make this work?

Thanks,
EMMIE

 

[PHV]

Something like this ?
For i = 1 To 60
strQryName = Format(i, "00") & "query"
DoCmd.TransferDatabase acImport, "Microsoft Access", prevMonth, acQuery, strQryName, strQryName, False
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

A new database will be created each month

Why on earth would you do that???? What are you going to do at the end of the year when they want a cumulative 12 month report?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[JerryKlmns]

Emmie1,

I have a same situation here. But I don't copy previous months queries over and over on every new month!

Split the database! My BE (named MIS_II.mdb) has month 's YTD data and FE (named MIS.mdb) the queries and the rest!

If they need previous month 's data they rename the MIS.mdb to MIS200506.mdb and MIS200505.mdb to MIS.mdb

If you want, code that renaming thing, to have control over the procedure. It 's simplier, safer and faster!

 

[Emmie1]

Thanks so much for the posts!!!

PHV - The counter code seems to work out. The problem that I'm having now is that my query names are descriptive "01combine prodcode", "02Import price", etc. But I can work around that!! Thank you very much!!!

LESPAUL - This database is a "prep database" it imports pricing, billing codes, and a whole lot more as well as summarizes data for about 100 thousand new records each month. Once it summarizes the data it is dumped into our billing system. If the user wants YRLY reports they can get those from the billing system. I don't keep YTD info because I don't want to bog down the db and they can get more accurate reports from another system.

JERRYKLMNS - Thanks for the idea! That sounds like something that I may want to try. Do you split the databases then link them?

This database has replaced a very long and tedious manual process. It would take our accounting department a week or mare to compile the data manually then data enter it. Now it takes about 30 mins. I am working on automating it further!! I'm the only person in my location that has any kind of access knowledge (which is by far lacking to say the least!!!) so any ideas are very very appreciated!!!

Thanks - EMMIE

 

[JerryKlmns]

Hi EMMIE,

Splitting creates two (2) databases. The Back End keeps data tables and relationships and Front End all the rest plus linked tables, those of Back End. When replacing MIS_II.mdb(containing 200506 data) by 200505.mdb (renamed to MIS_II.mdb), since the location of the .mdb file is the same and the tables (names and strusture) don't change, then there is no need to relink (delete and link again) or refresh the links (through code or manualy)! That way storage is minimized. And if FE resides on every user 's PC, forms and reports run faster which is the main reason to split a database on a network. The only thing to worry about, is checking for updated versions of FE. A topic also covered in tek-tips (I think there is a FAQ)! But since you are MASTER BLASTER #1 in your location (a previledge I also enjoy in my place), then new versions won't come up unexpected!

Coding keeps brain active!
Gerassimos