Here is another link that is basically exactly what you are wanting to do...
http://www.programmingmsaccess.com/Samples/VBAProcs/VBAProcsToAssignAnAD ORecordsetToAReport.htm -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Saiful Sent: Wednesday, January 25, 2006 10:13 PM To: [email protected] Subject: [ms_access] Re: How to read DSN Thanks Rick, I'll try it first. --- In [email protected], "Rick Justis" <[EMAIL PROTECTED]> wrote: > > 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 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/
