MSAccess table too large! 1

[gimpie1941]

I have a MSAccess97 database with over 500,000 records (yes, a half million) and it 'crawls'.! How can I convert the table to SQL table and still use the MSAccess frontend? I have 50 or so forms, 30 odd queries and over 50 reports. There are macros and visual basic code imbedded in the forms and reports. Can I just convert the tables (there are about 20) to SQL and just link them?
Also where would I find someone who could do this?
I wrote the MSAccess database (over two years) and am not anxious to learn enough SQL to do it over again.

Jim

 

[chrissie1]

Belive me if you want to convert to sql- server you will have a lot of work.
The tables convert pretty much automatic. He just forgets to set a primary index. But this is solved quickly.
The queries are a whole other problem some will convert with no problem, others cant be converted (the once with lots of functions in them)
The forms normally should work fine
the reports also
the only thing what you will have to look at is the code sql-access and sql-sqlserver are different. The way of calling recordsets is different you will have to change this.
But its worth it in the end.
just a tip
for sql-server 7.0 use access 97 and above
for sql-server 2000 use access 2002 and above
and BTW if you want to experiment you also have MSDE wich is a lite version of sql-server and it comes with office it is good to learn and has the same engine. the only difference is the interface or the lack of and its limited to 2Gb. "What a wonderfull world" - Louis armstrong

 

[perrymans]

SQL 2000 use Access 2002? As in Access XP? What problems are there in using Access 2000? My company has an awful dBase in 2000 and about 8 SQL 2000 licsences for an upgrade. But they have already purchased so many Access 2000's. What kind of problems are to be expected?

Also, migrating to SQL 2000, does he have to convert his queries as well? Can't they be left alone in the Access front end?

Sean.

 

[chrissie1]

yes access 2002 = access xp
the problem with access 2000 and sql server are numerous for one you cant create tables from within access 2000 it doesn support sql functions and so on.
On the other hand if you install the sevice packs it runs a bit better but with access xp it works great

and youdefinitly need to convert the queries, because if you switch to sql-server and adps the adp will take the tables and the queries from your server and that means your queries will be in T-sql and no longer in access sql witch makes a big difference in syntax. "What a wonderfull world" - Louis armstrong

 

[allanon]

I found that as long as you go through ODBC to the SQL Server db then most queries work fine. We have a VERY large db with an Access FE. Originally it was just access and was converted. Most of the queries work. ODBC translates most things so for the most part the move is not bad. Where you migh run into problems is getting your SQL Server indexes set up. These are very important with the Access FE. If these are set up right then the performance is great. If these are not then set up right then your app will just crawl.

 

[chrissie1]

sorry, but why do you use odbc to connect to sql-server if you use access. Access has ADO so you can make the connection much faster and better, use access project (adp) and you will see it becomes a lot clearer and a lot easier to work with. it saves on processor time because you dont need odbc to translate. "What a wonderfull world" - Louis armstrong

 

[perrymans]

So if I have a current pure Access 2000 db, and all Access 2000 licenses, how do I go about creating a back end for SQL Server 2000? From scratch on SQL 2000? An Access FE (even if new) would still access SQL 2000 won't it? And using ADO?

Thanks. Sean.

 

[chrissie1]

first of all if you have access 2000 you need to create your database on the sqlserver. set the security level you can choose between nt-security and sql-server, i choose nt.
then give permissions to certain users as needed. perhaps even create the table with the enterprise manager if installed on your local computer.
Then you can start creating the frontend. instead of creating a new empty database open a new project with an existing database, dont forget to install the clienttools on each computer , if you forget to install them everything seems to work fine but creates errors (weird errors).
then access will ask to set the connection after first asking where to save the adp (do this locally) in the connection wizard you ive the name of the server dont worry about the combolist being empty it always is. then set the securitylevel. if you have done this correctly you should see the databases on that server for witch you have access then click ok and if everything is ok you will see the tables and queries on the server like they are local. "What a wonderfull world" - Louis armstrong