table or query? temp table? (1 Viewer)

[mane_uk]

Hi,

I need to create an excel spreadsheet with the data entered in my database based on 2 dates (start and finish) provided by the user through a form.

My database is designed with FE/BE but there are no linking tables and I want to keep it this way.

What is the best approach: create a table "on-the-go" using VBA - by that I mean create the table, populate with data, export it then delete the table, create a permanent table on the BE which I could empty at start, populate via VBA, then export (probably then delete the data) or create a query in the BE?

Thanks
mane_uk

 

[CBrighton]

Not sure what you mean by having a FE/BE setup with no linking tables. How can the data be in the BE and the FE access it without linking the tables?

Anyway... a query should be fine for what you want.

If the only criteria is the date then it's as simple as creating a query with the fields you want and adding the following to the criteria of whichever date field you want to use:

Between [Enter start date] and [Enter end date]

The user will then be prompted to enter a start and end date each time the query (or any report which it is bound to) is run / opened / printed.

 

[mane_uk]

Hi CBrighton,

All my communication between FE/BE is done via VBA - open database/recordset, etc.... I had the same question posted back to me in another thread when I mentioned the FE/BE not being linked and it was mentioned that I am loosing a lot of what Access offer out of the box, that I should then use another tool to build what I want... anyway, MS Access is the only available tool here and the connection to link the FE/BE in my network drive was taking 10+ minutes, while with the approach I used it takes less than 1 minute and all the work itself is also a lot faster...

Anyway, back to the query/table question... thanks for the reply!!
If I choose the "query way" as you mentioned, I have about 15 tables that will be included into the query. I will then create a form which will trigger the query... my question is how can I open the query passing the values of the form? and then how can I export it to my Excel spreadsheet - does the transferspreadsheet command works on queries or only tables?

Thanks

 

[DCrake]

Do a search on CopyFromRecordset

 

[CBrighton]

re: FE/BE, I'm no expect as my split databases had horrific performance issues until we got an SQL server, however I believe the key is having a single VBA recordset open all the time the database is (i.e. in the background of the switchboard). Without this the database is constantly opening and closing connections to the BE. If your solution has a constantly open connection then I expect the speed difference of VBA vs. linked is minimal but I expect linked is the more standard method.


Re: the query, when you have a form with controls open (e.g. the one you want to use for the user to input criteria) open a query in design mode. Add a table and a field then right click in the criteria field and click build.

On the left hand side of the expression builder is a list / tree view which should include forms. if you fully expand this you will see you can easily add a control as criteria for a query just by double clicking the control you want for that field.