Print Page | Close Window

To covert MS Access to SQLServer

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=11031
Printed Date: 11 August 2020 at 11:22pm
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com


Topic: To covert MS Access to SQLServer
Posted By: padoxky
Subject: To covert MS Access to SQLServer
Date Posted: 29 June 2004 at 10:00am

Hi

I always wanted to ask this question that troblues me. I always use MS Access to write my application now I want to start using SQL SERVER in my application.

And I don't know how to change my connectionstring that is using MS Access to SQL Server connectionstring.

I have SQL server installed in my system.
Please, help me out.

Thanks
padoxky



-------------
NgWebDesigns



Replies:
Posted By: Mart
Date Posted: 29 June 2004 at 11:24am

Firstly you need to convert your MS Access Database to an SQL server db, for this you could use this walkthrough

http://www.devjunkies.com/?name=viewart&tid=60&type=1 - http://www.devjunkies.com/?name=viewart&tid=60&type= 1

After that you just change your connection string, to something like:

Server=server;User ID=user;Password=pass;Database=dbtouse;

If you need a differant connection string you could try

http://www.connectionstrings.com - www.connectionstrings.com



Posted By: Semikolon
Date Posted: 29 June 2004 at 1:55pm
you may also need to change some of the sql statements.. like the booleans True and False in Access should be 1 and 0 in SQL Server..

if you get any problems, it's just to come back and ask in a nice way


Posted By: padoxky
Date Posted: 30 June 2004 at 1:56pm

Thank you very much.

But I have found out that there are many different connection using SQL SERVER.

With DNS
<% dbconn="Provider=SQLOLEDB.1;UID=user;Password=password;Initi al Catalog=DSNname;Data Source=IPaddressoftheSQLserver,PortNumber" %>

OLEDB

<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=myda tabasename "
%> 

WITH DNS

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DSN=DSNname;UID=user;PWD=password;DATABASE=mydatabasename"
%> 

WITH OUT DNS

<%
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=myd atabasename"
Conn.open DSNtest
%> 

which one is the best to choose?
Plz, help!

padoxky



-------------
NgWebDesigns


Posted By: dpyers
Date Posted: 30 June 2004 at 2:10pm
The last one is the one to use for most web applications.

-------------

Lead me not into temptation... I know the short cut, follow me.


Posted By: Semikolon
Date Posted: 30 June 2004 at 2:11pm
use the ADO Connection Object and open a connection with the SQL OLE DB Driver

Set objADOCon = Server.CreateObject("ADODB.Connection")

objADOCon.Open "Provider=SQLOLEDB;Server(OR Data Source)=sqlserver;User ID(Or UID)=username;Password(Or PWD)=password;Database(Or Initial Catalog)=database name;"


Posted By: Mart
Date Posted: 30 June 2004 at 2:12pm
OleDb is the fastest connection, I would use that


Posted By: Semikolon
Date Posted: 30 June 2004 at 2:13pm
Originally posted by dpyers dpyers wrote:

The last one is the one to use for most web applications.


The SQL OLEDB Driver is faster.....
























I think


Posted By: Mart
Date Posted: 30 June 2004 at 2:40pm
Is there an echo in here?


Posted By: Mart
Date Posted: 30 June 2004 at 2:43pm
P.s. DSN connections are slower than DSN-Less connections


Posted By: padoxky
Date Posted: 30 June 2004 at 2:45pm

Thank you I think I will use OLEDB for my application.

Can you encrypt your connetionstring?
Also is it wise to add propertise to your connecting string to Prompt uid and pwd?

Plz help.



-------------
NgWebDesigns


Posted By: Mart
Date Posted: 30 June 2004 at 2:50pm

You can encrypt the connection string, but there is not a lot of point. Instead you can use a Trusted Connection to your SQL Server.

If you use a Trusted Connection you would not need to prompt for a Username/Password either.



Posted By: dpyers
Date Posted: 30 June 2004 at 2:51pm

By bad - I was picking up on the "Without DNS" in the last one, missed the SQL driver.

You'd want to use SQLOLEDB without DNS.



-------------

Lead me not into temptation... I know the short cut, follow me.


Posted By: padoxky
Date Posted: 30 June 2004 at 3:03pm

 Dpyers

Are you saying that I should use the last one?



-------------
NgWebDesigns


Posted By: Mart
Date Posted: 30 June 2004 at 3:08pm

He is saying what I said, you should use a DSN-less OLEDB connection . . . I.e.

<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=myda tabasename "
%> 



Posted By: padoxky
Date Posted: 30 June 2004 at 3:11pm

Thank you very much I now understood.



-------------
NgWebDesigns


Posted By: dpyers
Date Posted: 30 June 2004 at 4:59pm
lol - I meant to say "My" bad - not "By bad"

-------------

Lead me not into temptation... I know the short cut, follow me.


Posted By: michael
Date Posted: 30 June 2004 at 11:03pm
Originally posted by Mart Mart wrote:

P.s. DSN connections are slower than DSN-Less connections
 
That is actually an untrue and way to general statement. A Programmer in my company proved me, that a ODBC connection from a COM Component, under certain circumstances, was 4.3x faster then ADO, ADOX or ADO.net. Basically I am just saying, you cannot generalize that, thus in the "web-world" is just easier to use something that does not require something done on the server.


-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker


Posted By: dpyers
Date Posted: 01 July 2004 at 1:59am

I prefer dsn-less connections though for maintainability. Seems that periodically you get the need to move hosts or servers or something. I structure db dsnless connections, mail server info, etc. in a config file so I can make all my changes in one place and they take immediately. A lot of hosts need you to go through them to set up dsn's which is a real pain.

But like michael said, circumstances vary. Depending upon what's happening in your world, one method may be better than another. For example, the Oracle 7 ODBC stuff was great, their 8i stuff was crap, and their 9+ stuff is good.



-------------

Lead me not into temptation... I know the short cut, follow me.


Posted By: padoxky
Date Posted: 01 July 2004 at 4:26am

Hosting  Oracle DB is costly than SQL SERVER. I don't think I will use Oracle DB cox I can't afford it.

But it's nice to use it.



-------------
NgWebDesigns


Posted By: edwards142
Date Posted: 25 February 2020 at 7:46am

I have some easy fixes to migrate or convert Access database to SQL server. Few days back even I also haven’t such kind of ideas. But after searching a lot for the fixes I found this informative post on how to do this task in an easy and effortless way.

Before staring up the conversion of Access database to SQL server, make all these setups

Make backup of your database.

Check that if you have plenty of disk space on the device or not; in which you will have to keep your upsize database.

Allow permissions on the SQL Server database.

Add unique index to each access table that you have, before upsizing it.

Check out the complete steps to convert Access database to SQL server from this post.

www.accessrepairnrecovery.com/blog/convert-or-migrate-access-database-to-sql-server




Posted By: WebWiz-Bruce
Date Posted: 25 February 2020 at 10:36am
Thank you for joining the discussion edwards142.

This topic is from 16 years ago, so I have a feeling that your reply maybe a little late as a solution for those forum members who posted in this topic.


-------------
http://www.facebook.com/WebWiz" rel="nofollow - Find Web Wiz on Facebook
http://twitter.com/WebWizUK" rel="nofollow - Follow Web Wiz on Twitter



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.03 - http://www.webwizforums.com
Copyright ©2001-2019 Web Wiz Ltd. - https://www.webwiz.net