Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 306881 - Last Review: April 9, 2007 - Revision: 5.1
How to programmatically change the connection of a Microsoft Access project
This article was previously published under Q306881
Advanced: Requires expert coding, interoperability, and multiuser
skills.
This article applies only to a Microsoft Access project (.adp).
SUMMARY
This article shows you how to programmatically change the
connection of a Microsoft Access project (ADP) file.
MORE INFORMATION
The easiest way to change the connection of an ADP is to do
it manually. You can access the current project's connection properties as
follows in Access 2002 or in Access 2003: on the
File menu, click
Connection. The dialog box that appears gives you options to change the
server name and security modes, among other properties.
Note In Access 2007, follow these steps to open the
Connection tab of the
Data Link Properties dialog box:
- Click the Microsoft Office Button, and then click Access Options.
- On the Customize tab, click All Commands in the Choose commands from list.
- Click Connection in the left pane, click Add, and then click OK.
- Click Connection in the Quick Access Toolbar.
However,
sometimes, you might want to automate the process. You may want users to
connect to a different SQL server for a specific section of your application,
or you may want to control the connection process upon startup so that you can
look for errors and handle them accordingly, perhaps by routing to another
server.
The following steps use a function that demonstrates how to
change the existing connection of an ADP by using the Access object model. The
function returns
True if it succeeds and
False if it does not succeed.
- Start Access, and then open the sample Access project
NorthwindCS.adp.
- In Access 2002 or in Access 2003, click Modules under Objects in the Database window, and then click New to open a new module.
In Access 2007, click the down arrow below Macro in the Other group on the Create tab, and then click Module. - Add the following code to the new module, and then save the
module:
Function ChangeADPConnection(strServerName As String, strDBName As _
String, Optional strUN As String, Optional strPW As String) As Boolean
Dim strConnect As String
On Error GoTo EH:
Application.CurrentProject.CloseConnection
'The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
strConnect = strConnect & ";user id=" & strUN
If strPW <> "" Then
strConnect = strConnect & ";password=" & strPW
End If
Else 'Try to use integrated security if no username is supplied.
strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
ChangeADPConnection = False
End Function
- In Access 2002 or in Access 2003 click Forms under Objects in the Database window, click New, and then click OK to open a new form in Design view.
In Access 2007, click Form on the Create tab. - Add a command button to the form.
- Set the OnClick property of the command button to the following event procedure:
Dim bCheckConnection As Boolean
'You must specify the correct parameters for your following server.
'username and password parameters are optional.
bCheckConnection=ChangeADPConnection("ServerName","DBName","UserName","PW")
MsgBox bCheckConnection
- Close the Visual Basic environment to return to the
form.
- Save the form, and then switch the form to Form
view.
- Click the command button to run the underlying
code.
You will receive a message box that says True if you supplied correct parameters for a connection in step 6.
-OR-
If you did not supply the correct parameters, you
receive the error message. When you click OK to the error
message, you get the message box that displays False .
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
| kbprogramming kbadp kbvba kbhowto KB306881 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please