Access Design Help/Opinions

[freddy231]

Hi,
I have to create an Acess DB that imports 3 flat files and creates 30 reports using various queries. I want to do everything in vb code with SQL statements, and just have a form that'll have the 30 report names w/ checkboxes to run/print/(and maybe)export the reports. I'm an OK vb programmer but unfamilar w/ Access.

1) I just want to understand the best way of doing this; Can I just write the code in SQL/VB w/o having to create non-coded queries/report layouts in the Access front end?

2) What are pages? This sounds like a possible replacement for reports, which are more or less dead ends; however, printing these reports en mass is definitely a key component of this program -- are pages of any benefit over reports in this case?

2) How would you recomend to create the front end for the check boxes; From what I understand, I should create an Access table to keep track of each button; is there anyway to hardcode these w/o creating a table. Any suggestions on the best approach or any code handy to index through check boxes to return values would be greatly appreciated to get me started.

3) I'm using MS Access 2003 but the under Tools>Database Utilities>Convert Database it tells me I'm using Access 2000, should I stay in this version or use 2003?

4) I had a problem the other day trying to type SQL into vb code. Do I need to declare DOA/ADO statements when I'm programming from a module within the DB - any threads avail. on this would be of great help. How to call up tables and queries from within the Access DB would also be very usefull.

Thank you all
Fred :duck:

[DKenny]

1) I just want to understand the best way of doing this; Can I just write the code in SQL/VB w/o having to create non-coded queries/report layouts in the Access front end?
If the reports are not dynamic, i.e. if the user doesn't need to pass parameters. I would recommend using the built in queries/reports, rather than writing SQL directly into your code. Editing the Query object is a lot simpler and less error prone that editing raw SQL statements.

2) What are pages? This sounds like a possible replacement for reports, which are more or less dead ends; however, printing these reports en mass is definitely a key component of this program -- are pages of any benefit over reports in this case?
Pages are basically web (HTML) views into the DB. IF report printing is a requirement - stick with reports.

2) How would you recomend to create the front end for the check boxes; From what I understand, I should create an Access table to keep track of each button; is there anyway to hardcode these w/o creating a table. Any suggestions on the best approach or any code handy to index through check boxes to return values would be greatly appreciated to get me started.
The way i've done this in the past is to use the "Tag" property of the checkbox to store the name of the associated report/query. Then in your print/view/whatever procedure, you can call that property to determine which objects to call.

3) I'm using MS Access 2003 but the under Tools>Database Utilities>Convert Database it tells me I'm using Access 2000, should I stay in this version or use 2003?
Depends, if any of your users don't have 2003, then you won't be backward compatable.

4) I had a problem the other day trying to type SQL into vb code. Do I need to declare DOA/ADO statements when I'm programming from a module within the DB - any threads avail. on this would be of great help. How to call up tables and queries from within the Access DB would also be very usefull.
I think you need to determin your approach (SQL vs. obects) before we getr too far into this one. Basic answer is yes you need to declare DOA/ADO statements if you plan to work with raw SQL, no if you plan to use the DB objects.

[freddy231]

Thanks a 10^10 for your reply. I was not enthusiastic with SQL statements in vb given my exp the other day.
There are no parameters from users.
I just looked at the objects in F1 and they don't look so friendly. I will probably be visiting quite often.

Also - is there anyway to look at the vb code when you create quries/reports using their gui editors.

Thanks again.
F

[salvelinus]

Alt-F11 will bring up the code IDE, if any, for forms & reports.
If you create a query in the designer, right click the upper pane of the query & select SQL view to see the SQL. A saved query doesn't have VBA code, as such, although you can call it using VBA code.
Right clicking a form or report (or controls on them) will bring up the properties box, where you can set things like data source, etc. It's possible, if not common, to have a form w/o code, and is common to have reports w/o code.
You can add parameters in the query designer in several ways. I usualy use SQL view, and put desired parameters in square brackets. Users will then be prompted with whatever message is in the brackets.
Actually, unlike DKenny, I usually use SQL in code. If I need user supplied parameters, I just prompt for them & store them in variables.