Below is pasted out of the help files of the vba window......
ConnectionString Property
Indicates the information used to establish a connection to a data
source.
Settings and Return Values
Sets or returns a String value.
Remarks
Use the ConnectionString property to specify a data source by passing a
detailed connection string containing a series of argument = value
statements separated by semicolons.
ADO supports five arguments for the ConnectionString property; any other
arguments pass directly to the provider without any processing by ADO.
The arguments ADO supports are as follows.
Argument Description
Provider= Specifies the name of a provider to use for the connection.
File Name= Specifies the name of a provider-specific file (for example,
a persisted data source object) containing preset connection
information.
Remote Provider= Specifies the name of a provider to use when opening a
client-side connection. (Remote Data Service only.)
Remote Server= Specifies the path name of the server to use when opening
a client-side connection. (Remote Data Service only.)
URL= Specifies the connection string as an absolute URL identifying a
resource, such as a file or directory.
After you set the ConnectionString property and open the Connection
object, the provider may alter the contents of the property, for
example, by mapping the ADO-defined argument names to their provider
equivalents.
The ConnectionString property automatically inherits the value used for
the ConnectionString argument of the Open method, so you can override
the current ConnectionString property during the Open method call.
Because the File Name argument causes ADO to load the associated
provider, you cannot pass both the Provider and File Name arguments.
The ConnectionString property is read/write when the connection is
closed and read-only when it is open.
Duplicates of an argument in the ConnectionString property are ignored.
The last instance of any argument is used.
Remote Data Service Usage When used on a client-side Connection
object, the ConnectionString property can include only the Remote
Provider and Remote Server parameters.
See Also
Visual Basic Example | Visual C++ Example | Visual J++ Example
Appendix A: Providers
Applies To: Connection Object
(c) 1998-2003 Microsoft Corporation. All rights reserved.
ConnectionString, ConnectionTimeout, and State Properties Example (VB)
This example demonstrates different ways of using the ConnectionString
property to open a Connection object. It also uses the ConnectionTimeout
property to set a connection timeout period, and the State property to
check the state of the connections. The GetState function is required
for this procedure to run.
'BeginConnectionStringVB
'To integrate this code replace
'the database, DSN or Data Source values
Public Sub Main()
On Error GoTo ErrorHandler
Dim Cnxn1 As ADODB.Connection
Dim Cnxn2 As ADODB.Connection
Dim Cnxn3 As ADODB.Connection
Dim Cnxn4 As ADODB.Connection
' Open a connection without using a Data Source Name (DSN)
Set Cnxn1 = New ADODB.Connection
Cnxn1.ConnectionString = "Provider='sqloledb';Data
Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn1.Open
MsgBox "Cnxn1 state: " & GetState(Cnxn1.State)
' Open a connection using a DSN and ODBC tags
' It is assumed that you have create DSN 'Pubs' with a user name as
' 'MyUserId' and password as 'MyPassword'.
Set Cnxn2 = New ADODB.Connection
Cnxn2.ConnectionString = "Data Source='Pubs';" & _
"User ID='MyUserId';Password='MyPassword';"
Cnxn2.ConnectionTimeout = 30
Cnxn2.Open
MsgBox "Cnxn2 state: " & GetState(Cnxn2.State)
' Open a connection using a DSN and OLE DB tags
' It is assumed that you have create DSN 'Pubs1' with windows
authentication.
Set Cnxn3 = New ADODB.Connection
Cnxn3.ConnectionString = "Data Source='Pubs1';"
Cnxn3.Open
MsgBox "Cnxn2 state: " & GetState(Cnxn3.State)
' Open a connection using a DSN and individual
' arguments instead of a connection string
' It is assumed that you have create DSN 'Pubs' with a user name as
' 'MyUserId' and password as 'MyPassword'.
Set Cnxn4 = New ADODB.Connection
Cnxn4.Open "Pubs", "MyUserId", "MyPassword"
MsgBox "Cnxn4 state: " & GetState(Cnxn4.State)
' clean up
Cnxn1.Close
Cnxn2.Close
Cnxn3.Close
Cnxn4.Close
Set Cnxn1 = Nothing
Set Cnxn2 = Nothing
Set Cnxn3 = Nothing
Set Cnxn4 = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not Cnxn1 Is Nothing Then
If Cnxn1.State = adStateOpen Then Cnxn1.Close
End If
Set Cnxn1 = Nothing
If Not Cnxn2 Is Nothing Then
If Cnxn2.State = adStateOpen Then Cnxn2.Close
End If
Set Cnxn2 = Nothing
If Not Cnxn3 Is Nothing Then
If Cnxn3.State = adStateOpen Then Cnxn3.Close
End If
Set Cnxn3 = Nothing
If Not Cnxn4 Is Nothing Then
If Cnxn4.State = adStateOpen Then Cnxn4.Close
End If
Set Cnxn4 = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
Public Function GetState(intState As Integer) As String
Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select
End Function
'EndConnectionStringVB
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On
Behalf Of Saiful
Sent: Wednesday, January 25, 2006 9:18 PM
To: [email protected]
Subject: [ms_access] Re: How to read DSN
Hi Rick,
can you show me the example?
--- In [email protected], "Rick Justis" <[EMAIL PROTECTED]> wrote:
>
> The best way to do it is in your program, have it set to connect to
> the db without the dsn so it wouldn't matter if it was the same or
not.
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED] On
> Behalf Of Saiful
> Sent: Wednesday, January 25, 2006 9:04 PM
> To: [email protected]
> Subject: [ms_access] How to read DSN
>
> Dear friends,
>
> I have this problem.
>
> I have 1 server and many pc's linked to it. Every pc's have a
> different system dsn name for the same database in the server. I have
> created a little msaccess program to produce a report.
>
> The problem arise when I want to distribute the program. Since the dsn
> name is not standard, it did not work when the program try to get the
> table in server.
>
> How can I use vba in access to read the system dsn in each pc to link
> the table.
>
> Thanks for all your help
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
Yahoo! Groups Links
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
