Access v MSSQL When to make the change

I.  The benfit you'll be ganing if there is a database bottleneck, in access all users would be affected, in sql not necessarily. In general you can say that a forum of you size might be the borderline. (Wild guess) might run for some more time on access but you'll never know when it crashes.

II.   1. It is very easy to upgrade. Either use DTS in SQL server to import your Access tables or use the Access upsizing wizard. (Create the sql forum before any of those options)

      2. see no. 1
      3. I did it before and it was completely uncomplicated. Run a test first if you want and if you have the resources but I never had a problem doing it.
      4. I don't know, how many hairs do you have now?

Yes very slow.

I think MSSQL is needed soon

55Mb Access database!!!

I would upgrade to Ms SQL Server as soon as possible, I'm suprised the forum still functions at all on an access database of that size.

As Access is a flat file database, it means that each tiume the database needs to be accessed the entire file needs to be opened and loaded into memory, putting allot of strain on the web server. As Ms SQL server, is just that a server database system you will notice a big increase in performance by upgrading to it.

Thanks for all the replies.

I've taken the plunge and got the hosting company to add SQL server for this site.  I've set up the SQL version of the forum on the server and everything works great with the empty database.

But... Now I am running into a problem in upsizing the old Access database.

Access is able to connect to the SQL Server database,  and  can see all the tables/modify what is in them.

But when I use the upsizing wizard it works away for a while then the report shows  'table was skipped or export failed' errors.  Then for tblTopic it shows :

[Microsoft][ODBC SQL Server Driver][SQL Server]There are no primary or candidate  keys in the referenced table 'tblForum' that match the referencing column list in the foreign key 'tblTopic_FK00'.

Trying to troubleshoot,  I tried a smaller database from another site I run,  again,  I ran into the same problems using the upsize wizard.   I was able to transfer the data from the smaller database simply through copying and pasting.  This worked fine and everything was working.  My problem with doing this for the larger database is that not all the records are sequential ( User acccounts have been deleted etc )  So if I copy and paste the data, the user id's in the SQL server database no longer match what was in the Access database.

Any suggestions would be very welcome!

Finally, In answer to some of the points above :

How many sites are on the server : Quite a lot,  but most are fairly low traffic/low bandwidth. equestrianconnection.com is the busiest site on there with around 3000 uniques per day.

How Much Bandwidth for forum : Not sure,  there's no bandwidth limit on the server so I haven't concerned myself with bandwidth usage up to now.

Bug with no Sig : Cheers for pointing that out,  will fix that as I upgrade to SQL Server

Cheers!