[AccessD] Access ADP - Setting connection

Hi Sander

That sounds like a request for a DSN file.
Here:
http://blogs.ittoolbox.com/visualbasic/munk/archives/database-access-made-simple-9149

you can study this example by Skip Munk:

Private Sub cmdADO_Click()
  'ADO objects
  Dim adoConn As ADODB.Connection
  Dim adoRset As ADODB.Recordset
  Dim adoField As ADODB.Field
  'Other variables
  Dim intFile As Integer
  Dim intCol As Integer
  Dim intRow As Integer
  Dim strInput As String
  Dim strConn As String
  Dim strData As String
  
  'Get a file number.
  intFile = FreeFile()
  
  'Open the file DSN for input\reading.
  Open App.Path & "\MS-SQL_Example.dsn" For Input As intFile
  
  'Loop through and read the file one line at a time.
  Do While Not EOF(intFile)
  
    'Read a single line of the file.
    Line Input #intFile, strInput
  
    'Ignore the first line of the File DSN.
    'Your connection string will through an error
    'if you do not eliminate that line.
    If (InStr(1, strInput, "[ODBC]") = 0) Then
      'Append the read line to the connection string.
      strConn = strConn & IIf((strConn = ""), _
                              strInput, ";" & strInput)
    End If '(InStr(1, strInput, "[ODBC]") = 0)
  Loop 'While Not EOF(intFile)
  Close #inFile
  
  'Initialized the ADO Connection Object.
  Set adoConn = New ADODB.Connection
  
  'Set the Connection String.
  adoConn.ConnectionString = strConn
  
  'Open the connection.
  adoConn.Open
  
  'Initialize the ADO Recordset with a query
  'executed via the Connection Object's Execute method.
  Set adoRset = adoConn.Execute("select * from authors")

  'Print the value of each field for each row in the recordset.
  Do While (Not adoRset.EOF)
    'Scan fields and display contents.
    For Each adoField In adoRset.Fields
      Debug.Print adoField.Name & " = " & adoField.Value
    Next
    'Move to the next record in the ADO Recordset.
    adoRset.MoveNext
  Loop 'While (Not adoRset.EOF)
  
  'Close the recordset and connection.
  adoRset.Close
  adoConn.Close
  
  'Deallocate all objects.
  Set adoRset = Nothing
  Set adoConn = Nothing
  
End Sub

/gustav


>>> accessd666 at yahoo.com 09-02-2007 09:40 >>>
Hi group,

I've got an ADP. In File Connection I've set a
connection to a SQL Server 2000 database.

I want (Read: DBA wants...) to create an ini file
which is in a secure location where the connection
info resides.

Is this possible?

I've created an AutoExec macro that executes a
function Main. This function reads an ini file and
creates a new connection:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

With cnn
      .Provider = "SQLOLEDB"
      .Properties("Data Source") = "xxxx\yyy01"
      .Properties("Initial Catalog") = "APP"
      .Properties("User Id") = "xxxx"
      .Properties("Password") = "yyyyy"
      .Properties("Persist Security Info") = False
      .Open
End With 

After this I excpect a list of tables that reside in
this database...however I do not see them.

I'm doing something wrong? Did I miss something?

TIA
Sander