Access to SQL Server - Step by Step

wrote: how many members is good enough for moving on to SQL server....... wat i want to know is that though it is well known that access supports small database sizes with fewer DB calls but exactly how much members & how many daily/simultaneoul hits R  gud enough to move onto SQL ????

This has been discussed to no end here. In general with 15 or more concurrent connections, you should move to Access. Number of members nor database size is as important as the concurrent connections. Now, the larger the database size, the slower it will run as Access is not as fast in indexing as SQL Server.

There are exceptions to the rules. Here are the stats from my largest forum, which runs on Access.

We have 14143 Forum Members
Avg New Members per day: 45.02 (excluding today)
New Members today (so far): 6 | Yesterday: 12
# of Members Visiting Site Today: 96 (excluding new members)
Launch Date of Site: February 4th, 2004 (314 days of operation)
We have 4173 Posts in 748 Topics in 21 Forum(s)

At peak times, I will have between 80-200 active users. Most of the time, I average around 15-20.

netmasterjohn wrote: Follow my second post, ignore the first step by step. The stuff that causes the "ADODB.Recordset error '800a0cb3'" is eliminated by following the steps in my second post. It is simply a matter of how the tables and stored procedures are created, doing things the way I did it the first time tries to do too many things at once and the Recordset can't handle it. Doing things the second way works great. Check out my site for proof. http://www.epcmedic.com

The simplest way I have found, but requires MS SQL Server and Access installed on your local machine and a second SQL Server at your web host, is:-

1. Create the database a blank SQL Server database at your web host
2. Run the 'msSQL_server_setup.asp' file to create the database tables, Stored Procedure's etc. on your SQL Server database at your web host
3. Use Enterprise Manager to delete ALL the newly created tables on the SQL Server database created at your web host, just leaving the Stored Procedures.
4. Now on your local machine open your original Access database in Access
1. From the Tools menu in Access, select the 'Upsize Wizard'
2. Choose the Upsize option to 'Create a new database'
3. Choose 'local as your SQL Server and 'Use Trusted Connection'
4. Next select ALL the tables to upsize
5. Leave all other options as default
5. Once your database tables have been upsized using this method, use the 'Export Tool' in Enterprise Manager to upload the tables to the SQL Server database at your web host which you created the Stored Procedures in earlier
6. That should be it

This is the only method that I have found so far that works for me.

I did look into witting an ASP application that could transfer files across, but the problem is that the primary key for many tables is an Auto number, which means if there are any deleted posts, topics, members, etc. it would screw up the transfer process.

Darn !