Limits with access 97 & 2000? 1

[merlin72]

Could someone tell me what the limit size of the data bases are. I think 97 is 1 gig. Is 2000 the same. Also is there a way around this.
Would have used the keyword search but it's still down.
Also, for a big data base should i use a different prog.
Regards
Jim.

 

[Mongooses]

I have been told that 97 is 2gb and 2000 is 4gb

 

[bigfoot]

You don't want to use Access for a database of that size. Do more then 4 people access this database? If so, you want to get Microsoft SQL Server. It's cheaper then Oracle.

Access was not designed to be more then a desktop database (That's comming from MS itself).

-Gary They never have to knock if your door is always open.

 

[merlin72]

I have done some small programs with access97. Would going to SQL be a big jump as far as learning to use it or is it about the same with some difference.
Regards
Jim.

 

[bigfoot]

SQL Server is a back-end database package. It has no user screens, or pretty front end stuff for your users. That's done by VB, Powerbuilder, Access, etc. What it does well is sort/store data. There is a SQL Server forum here, you might try asking that question there.

BTW: I mentioned Access because Access can be used as a front end with any database that supports ODBC.

Hope this helps...



They never have to knock if your door is always open.

 

[32]

I would humbly disagree with the notion that Access is only ment to be a desk top application. We use several Access databases in conjunction to do our weekly monthly and quarterly sales reporting. These sales reports go all the way down to the SKU level and generate 100's of reports. This is all done using Client Access and Access 2.0!!!! The file size in 2.0 is only 1 gig thus we use several databases in the back end for storing data. Typically we are able to put around 1.3 million records into each db shell. I will tell you that Access is not fast at all when it comes generating reports so we usually kick of the program every night instead of running during the day time.

So as far as I'm concerned if your savy in Access and you relize the limitations of Access you should be able to build a Database(s) that can preform reporting jobs. I would not try to use Access for transactional functions at this size.

Rich

 

[bigfoot]

I was only stating what a MS tech support engineer told me on the phone. We too use access 97 for gathering orders from our AS/400 and creating "little" access databases for our salespeople to download.%-)

-G. They never have to knock if your door is always open.

 

[gwinn7]

Your MS Tech Support Rep must be mistaken.

Our office has deployed our custom Access app among 20 users with an overall database size of just over half a gigabyte. Our largest table has 90,000 + records with 100 + fields wide. We are able to use it with reasonable performance over our multi-user network.

It amazes me that a tech support rep would say such a thing when Access has been developed with multi-user capabilities etc. For a small business environment Access DOES fit the bill.

Gary
gwinn7

 

[bigfoot]

How did you set up the multi-user? Access97 did not handle it so well. I just started working with 2000, and I'm in the process of converting all my apps over to 2000.

-G. They never have to knock if your door is always open.

 

[gwinn7]

We have several backend Access MDBs that serve as several data repositories for lookup tables etc. These MDBs are on a shared drive. The Access custom app is a compiled MDE that accesses the MDBs via linking the tables inside the MDE. The JET Database Engine does the rest.

As you can see, its pretty straight forward.

Gary
gwinn7

 

[bigfoot]

oh, I see. So the multi-user logic is on your frone end?
What are you doing regarding record locking?
I was told access is a nightmare on this issue. I'm an as/400 / SQl Server person.
Is your compiled access app written in access, or like VB?

BTW: Thank you for the help. I'm giving Access another chance.

-Gary They never have to knock if your door is always open.

 

[gwinn7]

Yes, we are using DAO with optimistic locking.

It may be a nightmare depending on how users need to access data, but for us, it works nicely.

The Access app is written using bound controls and DAO depending on which part of the app you use. I am not sure what you mean by "written in Access, or like VB". Its definitely an Access compiled MDE with VBA code.

However, I am completely in favor of using SQL Server or some other client/server DBMS when it comes to anything significantly larger scale than what we have.

Gary
gwinn7

 

[bigfoot]

What do I need to do an Access compiled MDE . They never have to knock if your door is always open.

 

[gwinn7]

To make an MDE, try this...

1. Give the Database Window focus.
2. From the Access menu bar, click 'Tools'.
3. Select 'Database Utilities'.
4. Select 'Make MDE File'.
5. Enter a place and a name for the MDE.
6. Done. The MDE should be compiled and created.

Once the MDE is created, no user can mess with the source code in the MDE. The MDB will remain as it was. So, essentially, the MDE is like the EXE and the MDB is the Visual Basic version of VBP (I am giving an analogy only! don't take that literally.)

Gary
gwinn7

 

[bigfoot]

That's cool! I never knew that was there. Is there anyway to make an exe out of it? I assume (bad word) that youy need the access engine installed.

Thank you so much. That is usefull.

-Gary They never have to knock if your door is always open.

 

[gwinn7]

You are welcome! Glad to contribute!

To my knowledge, no, you can't make an EXE out of it.

Yes, you need MS Access installed in order to run the MDE.

Gary
gwinn7

 

[bigfoot]

Thanx

Hope to be of some help to you someday.

BTW: My fields are SQL Server, VB, ASP, JavaScript, Dreamweaver, InterDev, (too many)

-Gary They never have to knock if your door is always open.

 

[gwinn7]

Thank you!

Gary, feel free to send me e-mail:

gwinn7@adelphia.net


Gary
gwinn7

 

[Rohdem]

FYI: Access databases cannot be made into exe files, but a installable program can be created using the Developer's version of Access. This will install a runtime version of Access on the machine when you install your database. There are no license issues with the runtime version, so you can distribute it freely with an apps that you create.
Mike Rohde
rohdem@marshallengines.com

 

[bigfoot]

I think I'll still stick with VB for the front end and Access on the back end.

Sometimes the simpler things in life are better.

-Gary They never have to knock if your door is always open.