Connect to an ADP using ADO...

Well I'm not connecting to SQL Server... I'm connecting to the ADP file... are you saying I need to connect to it as if it's SQL Server?


I tried using a SQL Server con string to connect to the ADP, but I kept getting this error:

[DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connection.


Here was the connection string I used:

con.Open "Provider=sqloledb;Data Source=C:\Temp\Test3.adp;Initial Catalog=MYDB;User Id=myuser;Password=mypass"


I can connect straight to the SQL Server just fine using that same exact string (replacing the adp path with the server name).


WATYF

Hold on, you can't connect from something else to the ADP file, since the ADP file itself has no local tables for you to connect to.

If you can connect to the SQL Server, why not use that connection to transfer data from a recordset into your table or text file?

I don't understand ... if you can connect directly to the SQL Server, why use an ADP? You should be able to connect from any ADO-aware host host (Excel, Access, VB, VB.NET, etc) and extract your data from there. Basically, the commands will be the same and you can cut out the overhead of an ADP. If you write class/standard modules with fairly generic language, you should have no trouble.

Or mayby I'm missing something here ...

Connecting to SQL Server should be easy. Do the following:
1. Make sure you have data in the SQL Server database tables.
2. Make sure you have a user in SQL Server that can access the SQL Server database.
3. Create a new Access Project (Existing Database) .
4. The project should initally display a Data Link Properties window.
5. Enter the required information and test the connection.
6. If the test connection succeeded, you are ready to go.
7. Create a new Access module under the Modules tab.
8. Paste the following into the vba window:
  Public Sub cnntest()
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset

    Set cmd = New ADODB.Command
    Set prm = New ADODB.Parameter
   
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "select * from table1"
        .CommandType = adCmdText
    End With

    Set rst = cmd.Execute
   
    Debug.Print rst.RecordCount

    rst.Close
    Set rst = Nothing
    Set cmd = Nothing

End Sub

9. Note: Modify the .commandText string to return records from your table.
10. In the immediate window, type cnntest, then press the Enter key.

You should get a record count returned from SQL Server. Let me know if this works.

OK... I left out some key details as to why I'm doing this...


I am quite familiar with using ADO to connect to SQL Server... and returning data to a recordset is not a problem. But I don't want a recordset... I want a table (or a text file). My problem, up to this point, has been finding an efficient way to get the data from SQL Server straight into an Access table or a text file... and there doesn't seem to be one.

I have tried BCP, but it requires granting way too much server-side permissions to a standard user... I've tried DTSs, but they have to be setup manually in the Enterprise Manager... and I've tried using an ADO connection, but all that gets me is a recordset...


This:


    Dim con As New ADODB.Connection
    Dim rs As ADODB.Recordset
   
    con.Open "Provider=sqloledb;Data Source=MYSERVER;Initial Catalog=MYDB;User Id=myuser;Password=mypass"
    Set rs = con.Execute("SELECT * FROM MyTable")


...only gets me a recordset (rs) populated with data. That does me no good. I want that data in a table or a text file, not a recordset.


If the database was in Access (instead of SQL) then I could use an SQL statement like this:

"SELECT * INTO MyTable IN "C:\Temp\MyDB.mdb" FROM SourceTable"

...but the "IN" operator doesn't work on SQL Server... So I wanted to try using an ADP, so I could run the SQL statements on an Access ADP, which would return data from SQL Server. It is only a theory, since I'm not sure it will work, but I can't even test the theory because I can't connect to the ADP to see if a "SELECT INTO IN" statement will work.


I hope that's clear. If anyone has other ways of getting data from SQL straight into Access, or getting data from a recordset into a table or text file, then that would be exactly what I'm looking for.


(and no... I do not want to loop through the recordset and do "INSERT INTO" statements for each record... that would be extremely inefficient considering the number of records I'm dealing with)



WATYF

In that case, dynamically create a table link to the SQL Server, run an append query and then run an insert query to select all records from the table link to your Access table, or else use TransferText to get the data out into a file. You can then delete the table link in code.

I had previously tried setting up linked tables, and then altering their "Connect" and "SourceTableName" name properties as needed. The problem I ran into was, when trying to open that linked table as other users on other machines, it would always prompt for a password for SQL Server, even though the user and pass were contained in the Connect string... (obviously I don't want that prompt.. they shouldn't even know that they're being connected to a server).

But I didn't create those tables from scratch using code... in my tests I just created them manually, and altered them with code. Perhaps if I create them with code, I can set up the connection dynamically at run time and avoid the prompt...


...so can you provide an example of how to create a linked table from scratch? Is it simply a matter of creating a new tabledef and then setting its Connect and SourceTableName properties?


WATYF

Hi WATYF,

If you want to stop the users from accessing the tables in the ADP create a View and give the user/role you use in your connect string permissions on the View, but remove all permissions for the tables.

If you want to connect to sql and return a table as a disconected object that can later be sourced by other apps, then rerurn a recordset and persist it as XML, you can then use the the msPersist diriver to open the XML as a recordset from any other app.

Public Sub Connect2Sql()
  Dim strDataConnect
  Dim objCn As ADODB.Connection
  Dim objRs As ADODB.Recordset
  Dim strSql As String
 
  Set objCn = New ADODB.Connection
  Set objRs = New ADODB.Recordset
 
  strDataConnect = adoConnectSQL("pwd", "sa", "Northwind", "(local)")
  strSql = "Select * from Employees"
 
  objCn.Open strDataConnect
 
  objRs.Open strSql, objCn, adOpenKeyset, adLockOptimistic
 
  With objRs
    Kill CurrentProject.Path & "\Employees.xml"
    objRs.Save CurrentProject.Path & "\Employees.xml", adPersistXML
    .Close
 
  End With
  Set objCn = Nothing
 
 
  ' Then in some other app open the xml as a recordset
  objRs.Open CurrentProject.Path & "\Employees.xml", "Provider=MSPersist;", , , adCmdFile
 
  With objRs
    ' Do stuff
    .Close
  End With
 
  Set objRs = Nothing
 
End Sub


Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource)

  ' Returns SQLOLEDB ADO connect string
  ' Uses SQL Server security

  Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource

  sProvider = "Provider=SQLOLEDB.1;"
  sPassword = "Password=" & psPassword & ";"
  sPersist = "Persist Security Info=True;"
  sUser = "User ID=" & psUser & ";"
  sCatalog = "Initial Catalog=" & psCatalog & ";"
  sDataSource = "Data Source=" & psDataSource
  adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource

End Function


Hope this helps

Alan

That's a very interesting feature... it's good to know that I can save off a recordset to a compact file (using adPersistADTG) and restore it later to a recordset.


But unfortunately, this doesn't address my issue. I'm am still left with a recordset as my end result, when the end result I'm looking for is a table.


WATYF

In your original post, you mentioned you wanted to write to a text file ... this will open a recordset and write it to a text file named "MyTextFile.txt" in the application's current directory. YOu could certainly alter this to accept a recordset, filepath, etc as arguments and therefore make this more "portable". Note that you need a reference to the MS ActiveX Datat Objects in your project to run this.

Function WriteToFile() As Boolean

  '/Purpose:
  '/Created: 10/9/2004 08:37 AM
  '/Created By: Scott

  Dim rst          As ADODB.Recordset
  Dim i            As Integer
  Dim strLine      As String
  Dim lngFile      As Long
 
  On Error GoTo Err_WriteToTable
  Set rst = New ADODB.Recordset
 
  lngFile = FreeFile
 
  rst.Open "SELECT * FROM tblClass", CurrentProject.Connection
 
  '/if the file already exists, kill it
  On Error Resume Next
  Kill CurrentProject.path & "\YourTextFile.txt"
  '/now open the new file
  On Error GoTo Err_WriteToTable
  Open CurrentProject.path & "\YourTextFile.txt" For Append As lngFile
    '/print the field names; if you don't want to print the field names, comment out the For - Next loop below
    For i = 0 To rst.Fields.Count - 1

        If Len(strLine) = 0 Then
          strLine = rst.Fields(i).Name
        Else
          strLine = strLine & "," & rst.Fields(i).Name
        End If 'If Len(strLine) = 0

    Next i
 
  Print #lngFile, strLine
 
  strLine = vbNullString

    Do Until rst.EOF

        For i = 0 To rst.Fields.Count - 1

            If Len(strLine) = 0 Then
              strLine = rst.Fields(i)
            Else
              strLine = strLine & "," & rst.Fields(i)
            End If 'If Len(strLine) = 0

        Next i

      Print #lngFile, strLine
      strLine = vbNullString

      rst.MoveNext
    Loop

Exit_WriteToTable:

  On Error Resume Next
  Close #lngFile
  Set rst = Nothing
  Exit Function

Err_WriteToTable:

    Select Case Err
      'case
      'case
      Case Else
        MsgBox Err & ":" & Error$, vbCritical, "basDevelopmentRoutines" & ": " & "WriteToTable"
    End Select

  Resume Exit_WriteToTable

End Function