Converting from MDB to SQL Server...

Hello all,

I am new here. I have been operating a website that is hosted by a company called Winsave. They provide MS Access database support, so I've been running my site and connecting to an MDB database using classic ASP.

My site has too much DB traffic for Access to support.

I want to switch to discountasp.net and use classic ASP to connect to the database, which I want to be SQL server.

In other words, I want to convert my MDB database to SQL server, switch hosts, and carry on.

Do any of you forsee any problems I might have, or do you recommend any tips for me by your understanding of what I want to do?

Thanks,
Ben

 

Ok - I was able to transfer the data. SQL server is much faster.

Ben

 

Hi Ben,
I am pretty new at web development and I curious to hear how you transfered your .mdb database to a SQL database. I have mdb database created and don't think that I can connect my site to it through discountasp.net. Any advice you have would be great.

Thanks,
Jon

 

quote:I have mdb database created and don't think that I can connect my site to it through discountasp.net</blockquote id="quote"></font id="quote">
why do you think you cannot connect to your access database ?

--
Steurm
www.steurm.net/steurm

 

First, let me say a bit about what I am doing.

I am running a matchmaking/singles site I created from scratch using .ASP classic, vbscript, and MS Access as the database back-end. I originally hosted it at winsave.com for their killer price on basic hosting options (and high bandwidth). But when I put an article about my site in the local paper and the traffic went to the site, MS Access died; page timeouts, DB limits, etc. It was then that I learned that Access claims to support up to 255 connections, but it will die with less than 10 concurrent sessions are attempted.

Long story short, I decided I needed a different database engine. I did some research. I could have gone the .PHP/MySQL route, but I would have had to re-learn programming, and MySQL has its quirks. Since I've been using the BASIC language since highschool, I decided to stay with .ASP/VBScript and upgrade to MS SQL Server. Although you can use any scripting language combined with ODBC to connect to any kind of database, I made an assumption that it would be easier to learn SQL Server than MySQL. So I dropped winsave and found discountasp.

It was only a matter of time before I figured out the (great) control panel features here, including the SQL server web admin panel.

I did some research on the web and found you could use the MS Access upsizing wizzard to convert your existing .MDB database to SQL Server format.

What must be understood is that your database (and it's name) are already set up on discountasp.net after you add sql server to your account. It happens automatically. Then you just have to populate the database itself. The beauty of this Microsoft way is that the population can be done automatically.

If you can't figure out how to get your SQL Server installed on your discountasp.net account, let me know and I'll talk about that.

Otherwise, it goes kind of like this:
Once your database is configured, you need to open MS Access so you can transfer data over to your SQL Server DB.

1. Open Access, then click on NEW. Then select NEW PROJECT (EXISTING DATA).
2. Give your project a name and location where it will be saved on your local hard drive.
3. The Data Link properties box appears. You will need to use information in this box that you find in your discountasp.net control panel. So multi task and open discountasp.net, leaving Access at the Data Link Properties dialog box, and login to your control panel.
4. After logging in, under Database Management, click MS SQL 2000. You will be shown something like this:
MS SQL 2000 Server Information
SQL Server Name mssql03.discountasp.net
Database Name DB_xxxxxx
Database Login xxxxxxxxx
Database Disk Quota (MB) 50
ODBC DSN xxxxxxxx_sqlConn
SQL Web Admin https://sqladmin.discountasp.net

5. Copy the SQL Server Name. Then multitask back to Access and paste the server name in the dialog box field #1 where it says server name. Then enter your account username in field #2 where it asks for the database username. Then enter your database password (initially, unless you have changed it, the SQL db password is the same password you use to get into the control panel) into field #3 where it asks for the db password.
6. After you do that, multitask back to the control panel and make a note of the database name (db_xxxxxxx). Now go back to Access and select that DB from the dropdown field where it says "select the database name on the server.
7. Then click OK.
8. If you did everything right, Access will show you a table view of the current (remote) SQL server database. Now you can import your data from your local .MDB file into this project by simply clicking FILE -> Get External Data -> Import.

Notes:
You can import the data and change the data types and characteristics of the structure, but you can't change the data itself. This "project" wizzard only serves as a connection to MS Sql Server to make structure changes or to add or delete tables.

If you want to change the SQL Server DB data content itself, you have to delete the tables and reimport them from your .MDB file and then reupload them to the SQL Server.

So if you get the SQL Server db populated and decide you need to mess with the data content itself, just open the same project file again, enter your password, and it will then connect to the same database, this time automatically. Then delete your tables and reimport new data.

If you want to start a new project at any time, just delete your project file and repeat the steps from above.

More Notes:
Discountasp.net does not support this upsizing wizzard and has a knowledge base article about this.

You can apparently download a trialware version of Microsoft's Enterprise Manager, which I have no knowledge about, but which apparently lets you do all the SQL server DB management right on your machine, including db content editing.

Even More Notes:
You can do much of your SQL Server db editing by going to: https://sqladmin.discountasp.net and entering the same db server, username and password information. This is useful for editing, but you first have to populate the database, which is what you do in the previous steps with the Project Wizzard.

Here, you can delete content (tables, rows) and edit the table structures and data types. You cannot edit content but you can insert new content. So if you need to edit a row, just write down its data, delete the row, then insert a new one with the correct data.

Finally, I just learned all this over the last 2 days. There may be other methods to managing the SQL Server db that I am not aware of - like if you can edit the SQL server db content some other way without having to delete and re-enter.

Maybe others will chime in.

Hope this helps. []
Ben

 

That worked great, thanks for all your help. I just currently switched from a server provider that only support MySQL and I did not want to learn that either. Now I have a huge project ahead of me (updating my site). Thanks again, and good luck. If you want I'll through a link on my site to yours.

Jon
TheUnderSound.com

 

I'm glad to help.

Your site looks good. I like how your boxes look like they have a drop shadow behind them. You demonstrate some design techniques there.

Ben

 

I don't think I could have asked for a better answer, I am going to get on this right away. Thanks so much for you help.

Jon Lavender
TheUnderSound.com

 

By the way:

Through the online SQL server control panel, we can send SQL statements to the database. With this tool we can easily edit content. I don't know if I mentioned that before.

I was just going to build a form for SQL statements, but the online control panel already has one.

Ben

 

As far as I know Access can take way more than 255 connections: For example:
http://www.vircom.com/Products/VOP_Radius/Benchmarks.asp

Rory

 

Yeah, that makes sense to me from my experience.

I may mention that I was using ODBC, not OLEDB.

While on the topic, is OLEDB preferred over ODBC? Should I change all my data access strings to OLEDB?

Ben

 

I found this to be quite useful:
http://www.4guysfromrolla.com/webtech/063099-1.shtml

Ben

 

Perhaps so. All I know is that when I used MS Access 2002 for my database backend, I got server timeouts, delays, denial of service, etc.

I highly doubt I had more than 10 or 20 people trying to look at the data content at the same time.

I think this discussion is definitely worthy of more replies and information. I would like to see some benchmarks for mysql too.

When I get back to the office, I'll post a comment by a book author on concurrent accesses for MS Access. Basically, I think it's John Kauffman in "Beginning ASP Databases" who says something like, "Although Access claims support for 255 users, you'd be lucky to manage 10."

In testing my old database backend, I think that was true.

Ben

 

Great. That seems to be the consensus among what I have read and also that it seems to handle data updates much better (reliability).

It does not support the same cursor sets as does ODBC, however.

Ben

 

Ive never had an issue with Access crashing, only problems were when I was uploading the DB and opened some records through the web site, then you have to restart, in Win9x anyway.

Never crashed on connections on any of my sites, though I dont have hundreds or thousands of user connections at one time. If you are getting hundreds of concurrent connections then ofcourse upgrade to SQL or similar.

this is basically what I have always used for access, though use Jet on some sites also:

SET conn = SERVER.CREATEOBJECT("ADODB.Connection")
conn.OPEN "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & SERVER.MAPPATH(dbfile)

Rory

 

I am currently trying my site using OLEDB instead of ODBC. We'll see how it works. I don't know if the difference is noticeable, but shouldn't OLEDB be more reliable for data updates?

Ben

 

I haven't had any "crashing" problems with it, either. But it sounds like you are referring to crashing as something happening to your own IIS webserver. I have only ran IIS briefly for some tests. I haven't seen any crashing problems with the remote server and Access.

I have wondered about some update anomalies though. I think I've seen some of that when I was using Access as the database back-end. It seems that for example, when a user is trying to update an entity in his/her record, and that update is also mysteriously performed on another record's entity.... Anybody ever seen that?

Ben

 

Of course it all depends on your database as well, how relational it is.... how your tables are structured, indexes, constraints, are you closing the connection all the time, or are you leaving a connection open here and there.

Ive found Access is a great tool for small databases in my years of development, however of you are looking at a site that is going to have in the mid to high tens of users then you might want to look at upgrading to SQL.

Just all depends on the complexity and the design of the database.

Michael

 

So far, I haven't seen or heard of a single anomalie since I upgraded to SQL server using OLEDB connections.

Ben

 

...on my own site, that is.

 

MS has an SQL administration tool. You can do quite a bit with it.

http://www.microsoft.com/downloads/...98-c57a-419e-acbc-2a332cb7f959&displaylang=en

(I know this is an old topic, but this might help someone browsing through the forum.)

 

HI. I just upgraded to Ms SQL from access. Anybody have any advice on how I can migrate the 100 or so queries I have? I just imported the tables using "file/get-external-data" but the queries didn't show up?

thanks for any advice you might have

de