setting up connection for external access project

[DishDash]

Hi,
I have an access Adp that I would like to change the SQL-server-connection
for from another Access database (mdb, accdb or adp). i can use the sample:

Application.CurrentProject.OpenConnection strConnect

to change the connection from within the access project itself. but how do i
do this externally. I don't want to use CurrentProject. Rather i would like
to use an application located on C:\myDb\Abc.Adp for example and change the
connection for that project.

you can preview the code for doing the current project on microsoft KB:

http://support.microsoft.com/kb/306881

thank you

 

[Sylvain Lafontaine]

Did you try to use Automation? See for example:

http://support.microsoft.com/kb/828550/en-us

http://support.microsoft.com/kb/192919/en-us

http://support.microsoft.com/kb/317113/en-us
http://support.microsoft.com/kb/317114/en-us


I don't know what you want to do but it's quite possible that the use of
Aliases in the Configuration Manager of SQL-Server would be a much better
solution.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[bcap]

I generally deploy ADP's which are not connected to *any* database, and
which make the connection when they start up.

How you "instruct" the ADP as to which database to connect to depends on
what you are trying to achieve. I usually do it by launching the project
from a vbscript and having the script pass in the server and database names,
but you could get the ADP to "read" the connection details from somewhere
else e.g. a file, another database, the system registry, anywhere that seems
appropriate really.

 

[DishDash via AccessMonster.com]

Thank you guys .. i think i wasn't specific enough in my first message .. the
program i am building is an installation program .. it will deploy MS-ACCESS
ADP PROJECT to multiple clients to different SQL server databases ... 1st
step of the installation is copying the ADP master file to different folders .
that is done .. then each one of those files will be open from the
installtion database and have it's connection string changed to the client's
database .. server name, database name ,, user name and passwords are stored
in a table inside the installation program .. i need to be able to open the
various copies of the project and automate the connection .. copy 1 connected
to db1 .. 2 to db2 and so on ... the example i found on the internet does it
but only if you changing the connection string for the current project ..
"Application.CurrentProject.OpenConnection strConnect" .. i want to change
"CurrentProject" to "C:\mytempdatabasefolder\Copy1.adp" .. then open that ADP
project change it's connection string and then close it ... then i have some
code that make that adp and ADE file .. lock it down and copy it to the
clients folders ... i had that program working for MDB files .. and it was
basically relinking the tables to a backend database, convert it to mde or
Accde, lock it down and copy it to a client folder. Now that we have some mdb
upsized to SQL server i need to be able to change the connection rather than
relinking the tables. ..

thank you ..

 

[DishDash via AccessMonster.com]

I think i found it:

Function testAppz2()

Dim strConnect As String

strConnect = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
"User ID=sa;PWD=haha;Initial Catalog=abcdb;" & _
"Data Source=mySQLServer;Connect Timeout=120"

Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")



With appAccess
.Visible = False
.OpenCurrentDatabase "D:\mytempdbfolder\abc.adp"


appAccess.CurrentProject.CloseConnection

appAccess.CurrentProject.OpenConnection strConnect
.CloseCurrentDatabase
.Quit acQuitSaveNone

End With

End Function

Thank you so much

 

[DishDash via AccessMonster.com]

Correction:

With appAccess
.Visible = False
.OpenCurrentDatabase "D:\mytempdbfolder\abc.adp"

Set currenproject = appAccess.CurrentDb
appAccess.CurrentProject.CloseConnection

appAccess.CurrentProject.OpenConnection strConnect
.CloseCurrentDatabase
.Quit acQuitSaveNone

End With