473,715 Members | 2,142 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I change the SQL server for Access ADP?

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?

Nov 13 '05 #1
4 14023
-----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. BaseConnectionS tring

It will return something like this:

PROVIDER=SQLOLE DB.1;INTEGRATED SECURITY=SSPI;P ERSIST SECURITY
INFO=FALSE;INIT IAL CATALOG=Databas e_Name;DATA SOURCE=Server_N ame;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. BaseConnectionS tring
strConnect = Replace(strConn ect, "Old_DB_Nam e", "New_DB_Nam e")
strConnect = Replace(strConn ect, "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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQed59oechKq OuFEgEQL+gACg9J TJlFbi09tumHCXW UisKjAtOC4AoLDt
PQfiwvUoyJElSur JtgjC74wy
=oD4o
-----END PGP SIGNATURE-----
rc*********@yah oo.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?

Nov 13 '05 #2
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. BaseConnectionS tring

It will return something like this:

PROVIDER=SQLOLE DB.1;INTEGRATED SECURITY=SSPI;P ERSIST SECURITY
INFO=FALSE;INIT IAL CATALOG=Databas e_Name;DATA SOURCE=Server_N ame;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. BaseConnectionS tring
strConnect = Replace(strConn ect, "Old_DB_Nam e", "New_DB_Nam e")
strConnect = Replace(strConn ect, "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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQed59oechKq OuFEgEQL+gACg9J TJlFbi09tumHCXW UisKjAtOC4AoLDt
PQfiwvUoyJElSur JtgjC74wy
=oD4o
-----END PGP SIGNATURE-----
rc*********@yah oo.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?


Nov 13 '05 #3
On Fri, 14 Jan 2005 07:52:51 GMT, MGFoster <me@privacy.com > wrote:

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

strConnect = strConnect & ", User ID= <user_id>, Password=<passw ord>"
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. BaseConnectionS tring

It will return something like this:

PROVIDER=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST SECURITY
INFO=FALSE;INI TIAL CATALOG=Databas e_Name;DATA SOURCE=Server_N ame;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. BaseConnectionS tring
strConnect = Replace(strConn ect, "Old_DB_Nam e", "New_DB_Nam e")
strConnect = Replace(strConn ect, "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.


Nov 13 '05 #4
-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQehNGoechKq OuFEgEQKEhwCg/PhHLWx9dIGxiLKx ywTGtXFZX04AoIf m
TA+xg5btouYfsbJ 6Yu7cMFln
=skeq
-----END PGP SIGNATURE-----
rc*********@yah oo.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?

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2630
by: Bennett Haselton | last post by:
I want to get an ASP.Net hosting account with my ISP, and I'm trying to find out what level of access to the server is requried in order for me to view the server in Server Explorer in Visual Studio .Net, where you can see the available database, expand it to get a list of tables, etc. What level of access is required? Of course you need to have an account with the right user rights, but is there some specific service that needs to be...
1
10977
by: 9628 | last post by:
error while trying to run project: Unable to start debugging on the web server. Access is denied. Would you like to disable future attempts to debug ASP.NET pages for this project? I have tried everything but I still get this problem!1 does anybody have any more ideas on how to get rid of this?? I have Checked the web config file and Debug is set to true. Checked Configuration manager and debug is set
3
2400
by: Colin Finck | last post by:
Hello! I need to backup a MySQL database (MySQL 4.0). But it is on a shared-hosting web server and so I don't have direct server access. I also have no phpMyAdmin installed. How can I backup the database? I think it is possible with PHP (version 5.0.2 is installed on the web server) to create a file with SQL commands which restore the data, but what is the easiest way to do it? Thanks in advance!
5
1795
by: Scott McDaniel | last post by:
I have a VB app which stores information in an Access 2000 db. The VB app handles multiple users (it's a logbook type of application, users share lookup tables but don't share information among themselves - IOW, I couldn't see your log entries and you couldn't see mine). We're adding a web interface to the package - basically a scaled down data entry interface to allow users to add log entries without being at their workstations. The web...
1
1876
by: Viswanathan S | last post by:
HI ALL! When user try to debug an Asp Web application, He got error "Unable to start debugging on the web server. Access is denied." The IIS server installed locally on the pc with Windows 2000 Professional . Administrator that try to debug on that same pc does not have any problems, Only simple user that try to debug recive this error. The user is member of the Debugger Users Group, and he can debug a Windows Application, But cann't...
1
1470
by: ganesh | last post by:
I installed dotnet and when i try to run a web application i get the error messagebox saying "Unable to debug on server,Access is denied.Would you like to disable the future attempts to debug".
3
1730
by: Jerry J | last post by:
Hi, My asp.net application must access a file that can be anywhere on the server's LAN. I am having a problem because the server, doesn't have access to files that are not on the server's local disk. I have done similar things using COM+ and COM dlls in older versions of asp. I would have the file access code in a COM dll, run the dll in COM+, and in COM+, enter a UN and PW that would have LAN privilege that the COM object would...
1
995
by: marks | last post by:
Hello, I did a install and re-install of VS.NET v1.1 and get the above. Web page runs, but no debugging. I tried MSDN listed fixes with no change. What 'access' does it want if I'm already the administrator? Mark
2
1575
by: Nuno | last post by:
How can i change the access modifier of a control from protected to public if the designer code is not visible to the developer? I tryed declaring it on the page but it throws a compile error saying that the control has already been declared as protected. Nuno
0
1170
by: asefa | last post by:
hello friends there, i need your help. at the moment i am trying to develope a website using asp.net. But i am tuckled with the following error message while trying to start run the programm. "it can`t start debugging on the web server?access is dennied." so could you help me what to do to solve such things, thank you for every trial you make to help me. wish you a nice time! Aseffa
0
8817
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9193
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9039
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7966
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6641
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5964
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4472
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4734
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.