How do I change the SQL server for Access ADP?

[rcmail14872]

I have MS SQL Server 2000 evaluation version on my desktop PC. I have
the SQL Server client tools (only) on the laptop. The two computers are
networked. I had an Access MDB database on my laptop, but I converted
it with the Access upsizing wizard to an Access ADP database with the
Access front-end on the laptop connecting to the SQL Server on the
desktop. Now I want to move from this test environment to the "real"
server. How do I go about this? Do I make the change from the laptop or
the desktop? Do I use the Access drop-down menu, Tools > Database
Utilities > Transfer Database? Or maybe from the desktop with: SQL
Server Enterprise Manager > Copy Database Wizard?

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You change the connection string on the .adp file.

Try this in the Debug window of the .adp file:

? currentproject.BaseConnectionString

It will return something like this:

PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY
INFO=FALSE;INITIAL CATALOG=Database_Name;DATA SOURCE=Server_Name;Use
Procedure for Prepare=1;Auto Translate=True;Workstation ID=WorkSta_Name

Just replace the Database_Name and the Server_Name w/ the names of your
db on SQL Server and server (computer) where it resides. You don't need
the Workstation parameter.

strConnect = CurrentProject.BaseConnectionString
strConnect = Replace(strConnect, "Old_DB_Name", "New_DB_Name")
strConnect = Replace(strConnect, "Old_Server", "New_Server")

After getting the old connection string & replacing the db name & server
name, you can close the connection using CloseConnection:

CurrentProject.CloseConnection

Then open a new connection to the "real" SQL Server db:

CurrentProject.OpenConnection strConnect

If you use SQL Login's instead of NT Authentication, get rid of the
INTEGRATED SECURITY=SSPI parameter in the connection string and use
this:

CurrentProject.OpenConnection strConnect, <user_id>, <password>

Substitute your user_id name and password.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQed59oechKqOuFEgEQL+gACg9JTJlFbi09tumHCXWUisKj AtOC4AoLDt
PQfiwvUoyJElSurJtgjC74wy
=oD4o
-----END PGP SIGNATURE-----
rc*********@yahoo.com wrote:

I have MS SQL Server 2000 evaluation version on my desktop PC. I have
the SQL Server client tools (only) on the laptop. The two computers are
networked. I had an Access MDB database on my laptop, but I converted
it with the Access upsizing wizard to an Access ADP database with the
Access front-end on the laptop connecting to the SQL Server on the
desktop. Now I want to move from this test environment to the "real"
server. How do I go about this? Do I make the change from the laptop or
the desktop? Do I use the Access drop-down menu, Tools > Database
Utilities > Transfer Database? Or maybe from the desktop with: SQL
Server Enterprise Manager > Copy Database Wizard?

[rcmail14872]

Thanks, after I manage to transfer/copy the database from the desktop
PC to the server, I will change the connection string in the ADP file.
As far as moving the actual SQL database, I'm not sure if I should use
the "Transfer Database" feature of Access or if I should use the "Copy
Database" wizard from the SQL Server Enterprise Manager. Or some other
way?

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You change the connection string on the .adp file.

Try this in the Debug window of the .adp file:

? currentproject.BaseConnectionString

It will return something like this:

PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY
INFO=FALSE;INITIAL CATALOG=Database_Name;DATA SOURCE=Server_Name;Use
Procedure for Prepare=1;Auto Translate=True;Workstation ID=WorkSta_Name
Just replace the Database_Name and the Server_Name w/ the names of your db on SQL Server and server (computer) where it resides. You don't need the Workstation parameter.

strConnect = CurrentProject.BaseConnectionString
strConnect = Replace(strConnect, "Old_DB_Name", "New_DB_Name")
strConnect = Replace(strConnect, "Old_Server", "New_Server")

After getting the old connection string & replacing the db name & server name, you can close the connection using CloseConnection:

CurrentProject.CloseConnection

Then open a new connection to the "real" SQL Server db:

CurrentProject.OpenConnection strConnect

If you use SQL Login's instead of NT Authentication, get rid of the
INTEGRATED SECURITY=SSPI parameter in the connection string and use
this:

CurrentProject.OpenConnection strConnect, <user_id>, <password>

Substitute your user_id name and password.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQed59oechKqOuFEgEQL+gACg9JTJlFbi09tumHCXWUisKj AtOC4AoLDt
PQfiwvUoyJElSurJtgjC74wy
=oD4o
-----END PGP SIGNATURE-----
rc*********@yahoo.com wrote:

I have MS SQL Server 2000 evaluation version on my desktop PC. I have the SQL Server client tools (only) on the laptop. The two computers are networked. I had an Access MDB database on my laptop, but I converted it with the Access upsizing wizard to an Access ADP database with the Access front-end on the laptop connecting to the SQL Server on the
desktop. Now I want to move from this test environment to the "real" server. How do I go about this? Do I make the change from the laptop or the desktop? Do I use the Access drop-down menu, Tools > Database
Utilities > Transfer Database? Or maybe from the desktop with: SQL
Server Enterprise Manager > Copy Database Wizard?

[Tom van Stiphout]

On Fri, 14 Jan 2005 07:52:51 GMT, MGFoster <[e-mail]> wrote:

The last part of your message is incorrect. For SQL Login, use:

strConnect = strConnect & ", User ID= <user_id>, Password=<password>"
CurrentProject.OpenConnection strConnect

I typically use this web page for connection strings:
http://www.able-consulting.com/ADO_Conn.htm

-Tom.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You change the connection string on the .adp file.

Try this in the Debug window of the .adp file:

? currentproject.BaseConnectionString

It will return something like this:

PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY
INFO=FALSE;INITIAL CATALOG=Database_Name;DATA SOURCE=Server_Name;Use
Procedure for Prepare=1;Auto Translate=True;Workstation ID=WorkSta_Name

Just replace the Database_Name and the Server_Name w/ the names of your
db on SQL Server and server (computer) where it resides. You don't need
the Workstation parameter.

strConnect = CurrentProject.BaseConnectionString
strConnect = Replace(strConnect, "Old_DB_Name", "New_DB_Name")
strConnect = Replace(strConnect, "Old_Server", "New_Server")

After getting the old connection string & replacing the db name & server
name, you can close the connection using CloseConnection:

CurrentProject.CloseConnection

Then open a new connection to the "real" SQL Server db:

CurrentProject.OpenConnection strConnect

If you use SQL Login's instead of NT Authentication, get rid of the
INTEGRATED SECURITY=SSPI parameter in the connection string and use
this:

CurrentProject.OpenConnection strConnect, <user_id>, <password>

Substitute your user_id name and password.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

All that matters is the back-end, not where the Access front-end
(interface) is. Therefore, just use the Enterprise Manager (EP) Copy
Database Wizard on the PC/Server that has SQL Server.

You can also detach & attach db files. See BOL "Attaching and Detaching
a Database" for more info.

If you're going into production, don't you think you should have more
than just an evaluation version of SQL'r?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQehNGoechKqOuFEgEQKEhwCg/PhHLWx9dIGxiLKxywTGtXFZX04AoIfm
TA+xg5btouYfsbJ6Yu7cMFln
=skeq
-----END PGP SIGNATURE-----
rc*********@yahoo.com wrote:

Thanks, after I manage to transfer/copy the database from the desktop
PC to the server, I will change the connection string in the ADP file.
As far as moving the actual SQL database, I'm not sure if I should use
the "Transfer Database" feature of Access or if I should use the "Copy
Database" wizard from the SQL Server Enterprise Manager. Or some other
way?