How to prompt to select a database from a directory to run a query and output a report

[EddieNada]

I have created a query based on one database but I need to select a database depending on user input from a directory as each month a new database will be created
i want the query to run on all database with similar name and there will be newer databases created every month

my sql query is:
SELECT SLS1220.BILL_NO, SLS1220.SESSION_NO, SLS1220.WAITER, SLS1220.PAY_TYPE, SLS1220.BILL_DATE, SLS1220.BILL_TIME, SLS1220.TOTAL, SLS1220.TAXES, SLS1220.AUTO_GRAT, SLS1220.DISCOUNT, SLS1220.RECEIVED, SLS1220.REV_CENTER
FROM SLS1220
WHERE (((SLS1220.BILL_NO)>=Nz([StartNo],0) And (SLS1220.BILL_NO)<=nz([EndNumber],100000000)) AND ((SLS1220.BILL_DATE)>=Nz([Enter Start Date],"01/01/1900") And (SLS1220.BILL_DATE)<=nz([End Date],"31/12/9999")) AND ((SLS1220.RECEIVED)>=Nz([Start Amount],0) And (SLS1220.RECEIVED)<=nz([End Amount],999999999)));

The SLS1220 next month will be SLS0121 and so on

[Bob Fitz]

Why create a new db. Why not stick to one db and just add data to it?

[Micron]

Agree with Bob, unless you have a very good reason for doing so - don't.

[EddieNada]

I do not have control of the creation of database. but they create one new database every month. I am trying to extract data for creating a report linking all databases the unique thng is the databases all start with 'SLS' followed by the numeric month and numeric year.

[Welshgasman]

I do not have control of the creation of database. but they create one new database every month. I am trying to extract data for creating a report linking all databases the unique thng is the databases all start with 'SLS' followed by the numeric month and numeric year.

So no input required?, you can calculate from when it is run?
I would hope that they put all the DBs in one folder.?

You could create a template SQl where you have SLSmmyy and the replace mmyy with the month and year number.?
You would need to be able to reference that table from your DB, which I presume you have already done?

However SLS1220 is a table not a DB?

You could either run the sql in VBA or amend the querydef sql if the query is stored.

[Micron]

I think the answer to the first post is use the file dialog file picker (e.g. msoFileDialogFilePicker). User navigates to the db file and picks it and the dialog returns the path to a sub or function. You will have to construct and execute the query sql in code though, because the db name being returned will be dynamic.

[Gicu]

Please have a look at the attached sample. You could add to the code to remove the old tables, either after running the report (and saving it as an external PDF) or after you update the query's SQL from the old one to the newly linked one.

Cheers,
Vlad

[EddieNada]

thanks how do i get the query to prompt for the db file

[Gicu]

Have you had a look at the file? The sub takes the mmyy as an argument, you could build a table with those and have a combo\list box for the user to select one, you could use an inputbox to prompt the user to type it, etc.

Cheers,
Vlad

[ssanfu]

Welcome to the forum...

How many tables are in each of the dBs? (SLS1020, SLS1120, SLS1220)
How many records in each of the tables?

I agree with Bob (Post #2)
It would be much better and easier to IMPORT the data from each of the dBs into one common dB, than to try and manage picking a dB and creating 1 or more queries.
However, I would have to see/would like to see examples of the dBs.

[EddieNada]

I did, one i am trying to figure how to pass the MMYY variables to the SQL plus how to prompt the Path and filename

[Gicu]

Eddie, you don't need to prompt the path and file name if you store it in the settings table like I suggested in my sample. All you need is the mmyy then you build the file name from that. Lets say your files follow the pattern SLS_MMYY.accdb and they are all stored in C:\SLS_DB_Files, you just need to prompt the user for the desired month_year you replace MMYY in the file name with that.
Build a table holding all your MMYY that you have data for (or if you have dates instead you can create a query that shows you the distinct MMYY by using the datepart functions) and in your form add a combo for the user to select the MMYY and a button to run the code. In the Click event of the button simply put:

Call vcRunReport(Me.cboMMYY) 'where cboMMYY is the combo to select the month\year

Cheers,