MS-Access limits

[FLabrecque]

New poster here, but be sure I'll be posting a few times each week since I'll now be using MS-Access for a lot of things. I would like to know, what size limits has an MS-Access 2000 database? If there is one, of course!

 

[IgorB]

Total size for database file:
MS Access97 - 1GB
MS Access2000 - 2GB

Make sure periodically compact db from menu Tools-Database Utilities-Compact and Repair

 

[FLabrecque]

Don't worry, already did! 2 Gigs hey? Not that bad. Since I may have many newbie questions, I'll post them in this thread.

Can you export an SQL query directly to Excel, without running a Macro or VBA Module? There seems to be a lot of this in my databases.

 

[The_Doc_Man]

Access capabilities - look in the Access Help option for keywords as follows:

Access 97 and earlier: Look for "Limits"

Access 2K and later: Look for "Specifications"

Can you export an SQL query directly to Excel, without running a Macro or VBA Module?

Absolutely. And if you wish to do specific formatting or ordering that does not match the table, or if you wish to JOIN two tables, this is the only really good way to fly.

 

[Mile-O]

I would like to know, what size limits has an MS-Access 2000 database?

While using Access, press F1 and then enter specifications - all the limits and other information is presented here.

 

[Mile-O]

A whole minute earlier, Doc.

 

[FLabrecque]

Thanks for the feedback. You've all been very useful. I'm mesmerized by the fact that running a query can automatically output the result in an Excel file. Any idea how it's done?

The help file isn't very useful on this. I would suppose the Query would have properties, stating such things.

 

[IgorB]

Export to Excel:

Be in Design Mode, select Query, right-mouse click--Export, scroll in drop-down Microsoft Excel 97-2000 and follow other screen in order to finish export.

 

[Mile-O]

Just right click on it, select Export, and change the file type to Excel. Give it a name and hit export.

 

[Jonathan Kok]

AFAIK, you can't have this automated by just running the query. You can either do this via code behind a button on a form, or by using a macro. I'll show you the macro way, since I can't remember the code off the top of my head. EDIT: Or, you could do what the other two said. Mine is more fun

First, create the macro. Select 'OutputTo' as the Action. In the Action Arguments section, select the Object Type (Query), Object Name, and set the Output Type to Microsoft Excel 97-2003 (*.xls). Enter a file name if you plan to use the same file name every time it runs. If you don't enter a file name, it will prompt you when you run it. Save it. Run it.

You could always create a button on a form to run the macro, if you want.

HTH