Web Wiz - Solar Powered Eco Web Hosting

  New Posts New Posts RSS Feed - To covert MS Access to SQLServer
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

To covert MS Access to SQLServer

 Post Reply Post Reply Page  123>
Author
padoxky View Drop Down
Groupie
Groupie
Avatar

Joined: 17 June 2004
Location: Nigeria
Status: Offline
Points: 78
Post Options Post Options   Thanks (0) Thanks(0)   Quote padoxky Quote  Post ReplyReply Direct Link To This Post Topic: To covert MS Access to SQLServer
    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
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304
Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post 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

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

www.connectionstrings.com

Back to Top
Semikolon View Drop Down
Senior Member
Senior Member


Joined: 09 September 2003
Location: Norway
Status: Offline
Points: 1718
Post Options Post Options   Thanks (0) Thanks(0)   Quote Semikolon Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
padoxky View Drop Down
Groupie
Groupie
Avatar

Joined: 17 June 2004
Location: Nigeria
Status: Offline
Points: 78
Post Options Post Options   Thanks (0) Thanks(0)   Quote padoxky Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
dpyers View Drop Down
Senior Member
Senior Member


Joined: 12 May 2003
Status: Offline
Points: 3938
Post Options Post Options   Thanks (0) Thanks(0)   Quote dpyers Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Semikolon View Drop Down
Senior Member
Senior Member


Joined: 09 September 2003
Location: Norway
Status: Offline
Points: 1718
Post Options Post Options   Thanks (0) Thanks(0)   Quote Semikolon Quote  Post ReplyReply Direct Link To This Post 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;"
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304
Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post Posted: 30 June 2004 at 2:12pm
OleDb is the fastest connection, I would use that
Back to Top
Semikolon View Drop Down
Senior Member
Senior Member


Joined: 09 September 2003
Location: Norway
Status: Offline
Points: 1718
Post Options Post Options   Thanks (0) Thanks(0)   Quote Semikolon Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply Page  123>
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.


Become a Fan on Facebook Follow us on Twitter Connect with us on LinkedIn Community Forums Web Wiz Blogs Web Wiz News
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Policy

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2020 Web Wiz Ltd. All rights reserved.