Query regarding dsn string in vba

Question

Below is the code to fill a list box in a VBA application :

Private Sub Form_Open(Cancel As Integer)

  ''#Populate list box control.
  Dim cnn As ADODB.Connection
  Dim strSQL As String
  Dim rst As ADODB.Recordset
  Dim strList As String

  On Error GoTo ErrHandler

  ''#Use DSN to Northwind. 
  ''#Modify connection and connection string as needed.

  Set cnn = New ADODB.Connection
  cnn.Open "DSN=NorthwindExample"
  strSQL = "SELECT * FROM Shippers"
  Set rst = New ADODB.Recordset
  rst.Open strSQL, cnn
  strList = rst.GetString(adClipString, , ";", ",")

  Debug.Print strList

  Me.lstShippers.RowSource = strList
  rst.Close
  cnn.Close
  Set rst = Nothing
  Set cnn = Nothing

  Exit Sub

ErrHandler:
  MsgBox Err.No & ": " & Err.Description, vbOKOnly, "Error"
  Set rst = Nothing
  Set cnn = Nothing
End Sub

I need to know what i need to put as DSN string? Where will I get the info?

What is adClipString here in this code?

Is there any option to populate list control without using DSN connection object since I am taking the values from the same access table?

Answer 1

Here is a link that contains the different connection strings for Access: http://www.connectionstrings.com/access

Something like this should work: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

Im not sure what adClipString is, it could be an undeclared variable or database column?

Matt

Answer 2

Here is the info on adClipString.

Basically, GetString method gets the content of the entire recordset into a string variable where columns will be separated by ";" and rows will be separated by "," (as per your code).

Regarding DSN - see Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC). One of the tab (I guess System DSN) is where ODBC based data source can be created and are listed.