Does anyone have any thoughts to Access as a large scale database? Can it be used effectively with thousands of transactions stored within? Can an Access Database be upgraded to SQL Server if need be down the road? I recently completed a database application in Access for my boss at one location and now we are looking into a larger scale version at another warehouse, can Access be relied on for large amounts of data? What problems, limitations can I expect down the road as the data and complexity of the operation grows. On a side note, are there any good books anyone can recommend on Visual Basic to go along with developing an Access application? Any ideas and thoughts or your experiences would be helpful. Thank you!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Access as a large scale operation 1
- Thread starter 426Hemi
- Start date
rorymurray
Programmer
While there are certainly many other experts here that can answer this properly, from reading other posts on here it seems to be relatively easy to convert to SQL server from Access.
With regards to books on VBA, go to Amazon.com and search for VBA and Access and you should come up with a number of recommendations. There are also other books available specifically on converting Access applications to SQL Server applications. One that I have recently purchased is "Access 2000 Developer's Handbook 2 Volume Set", and another I am considering buying is "Access 2000 VBA Handbook". I believe these give good advice on scaling up your applications to industrial strength applications.
Good luck.
With regards to books on VBA, go to Amazon.com and search for VBA and Access and you should come up with a number of recommendations. There are also other books available specifically on converting Access applications to SQL Server applications. One that I have recently purchased is "Access 2000 Developer's Handbook 2 Volume Set", and another I am considering buying is "Access 2000 VBA Handbook". I believe these give good advice on scaling up your applications to industrial strength applications.
Good luck.
-
1
- #3
SQL server is designed to handle large numbers of people accessing at the same time. In addition, it has much better capabilities for backing up data. Access databases cannot be backed up if they are in use but MS SQL databases can because the database engine does the backing up and knows how to cope with items in use.
If you think there is a possibility of moving to MS SQL Server later then I would avoid spaces in table names and field names as SQL Server does not permit these. Usually people who are not aware of this have to change all these names and this impacts on all your queries, reports and VBA code.
It is not too difficult to move an Access application onto SQL server. You should design the Access application as two databases. One will be a frontend supplied to users and containing queries, forms and reports. The other will be a backend sitting on a shared drive and containing the tables. You link the two together. Moving to SQL Server is basically a question of changing the front end links to point to SQL Server which is relatively painless.
Ken
If you think there is a possibility of moving to MS SQL Server later then I would avoid spaces in table names and field names as SQL Server does not permit these. Usually people who are not aware of this have to change all these names and this impacts on all your queries, reports and VBA code.
It is not too difficult to move an Access application onto SQL server. You should design the Access application as two databases. One will be a frontend supplied to users and containing queries, forms and reports. The other will be a backend sitting on a shared drive and containing the tables. You link the two together. Moving to SQL Server is basically a question of changing the front end links to point to SQL Server which is relatively painless.
Ken
gusbrunston
Programmer
Hi Ken:
I have backed up a database, while in use, for quite a while. On this site, there is an excellent FAQ by Jimmy the Geek on how to do this.
Is there anyway to set Access to recognize a name change and "cascade" the change to forms, queries, etc, in preparation for going to SQL? Gus Brunston![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com
I have backed up a database, while in use, for quite a while. On this site, there is an excellent FAQ by Jimmy the Geek on how to do this.
Is there anyway to set Access to recognize a name change and "cascade" the change to forms, queries, etc, in preparation for going to SQL? Gus Brunston
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com- Thread starter
- #5
I have had a look at Jimmy's backup FAQ. So far as I can tell it will work if no one is using the database and it will fail if someone is using it. At the end of the day it is using Windows to copy the file and Windows will always refuse to copy a file that is in use. This is not a criticism of Jimmy's code which is fine if you understand Windows limitations - it's just the way windows is.
With SQL Server the database engine copies each table record by record whether or not the tables are in use. If someone is updating several tables together as part of a transaction it will capture the data for all tables as at the start of the transaction so tables are always consistent.
So my advice that SQL Server backup is more robust than Access backup still holds, particularly if the database is very active.
Access 2000 has the ability to track name changes within a database so that queries and reports stay consistent with tables. The initial implementation had some unintended side effects but these were fixed in the first Office 2000 service pack. There is a tick box to activate under tools options.
Ken
With SQL Server the database engine copies each table record by record whether or not the tables are in use. If someone is updating several tables together as part of a transaction it will capture the data for all tables as at the start of the transaction so tables are always consistent.
So my advice that SQL Server backup is more robust than Access backup still holds, particularly if the database is very active.
Access 2000 has the ability to track name changes within a database so that queries and reports stay consistent with tables. The initial implementation had some unintended side effects but these were fixed in the first Office 2000 service pack. There is a tick box to activate under tools options.
Ken
gusbrunston
Programmer
Well,
I just have to say something.
Jimmy has two backup FAQ's on this site. One uses FileCopy and the other uses FileSystemObject. The one that uses FileSystemObject has been backing up my database with the file open on the server for three work stations every day for months. So it's sort of like reading that a study of the drawings for an F16 reveals that it won't fly right after landing one on the carrier deck. Gus Brunston An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.com
I just have to say something.
Jimmy has two backup FAQ's on this site. One uses FileCopy and the other uses FileSystemObject. The one that uses FileSystemObject has been backing up my database with the file open on the server for three work stations every day for months. So it's sort of like reading that a study of the drawings for an F16 reveals that it won't fly right after landing one on the carrier deck. Gus Brunston
An old PICKer, using Access2000. I manage Suggestions welcome. padregus@attbi.comDuring the Falklands war the British defence systems were found to be limited as to the number of aircraft that could be tracked and attacked at the same time.
It is a general characteristic of Access that it does have limitations as to the number of users it can handle and what you can do while users are connected. For example, you would not want to compact and repair a database that is in use.
In a localised operation you may be able to carry out processes such as backup or compacting while everyone is asleep. In an international business or one where access can be from anywhere in the world such as a web application it becomes harder to guarantee that Access will not be in use.
A lot of companies who use tape backup find that some files are never backed up because of the in use restrictions and the tape software normally logs these.
The real test of the F16 is not whether it landed but whether it can take off again. A lot of people think they are backing up. How many actually test a restore to prove it worked?
Ken
It is a general characteristic of Access that it does have limitations as to the number of users it can handle and what you can do while users are connected. For example, you would not want to compact and repair a database that is in use.
In a localised operation you may be able to carry out processes such as backup or compacting while everyone is asleep. In an international business or one where access can be from anywhere in the world such as a web application it becomes harder to guarantee that Access will not be in use.
A lot of companies who use tape backup find that some files are never backed up because of the in use restrictions and the tape software normally logs these.
The real test of the F16 is not whether it landed but whether it can take off again. A lot of people think they are backing up. How many actually test a restore to prove it worked?
Ken
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Question