Connect to an ADP using ADO...

Hi there... I'm having a horrible time getting data out of my SQL Server. I need a completely flexible, 100% code-based method of extracting data into a text file or Access table, but none seem to exist (that don't have significant setbacks). DTS's require front-end setup in Enterprise Manager, BCP requires command line access on the server, etc. etc.

So... after giving up on being able to get data directly from the SQL Server,  I decided to set up an ADP and connect it to my SQL Server (kind of as a "middle man" approach)... So now, I would like to connect to that ADP using ADO (from with Excel, or Access or whatever). So what provider do I use? (the Jet provider didn't work) What's the connection string needed to do this?


P.S. I'm working in Access 2000
LVL 11
Who is Participating?
WATYFConnect With a Mentor Author Commented:

I finally found what I'm looking for... it was a single SQL Statement.

SELECT * INTO [AccTable] FROM [odbc;driver={SQL Server};server=MYSERVER;database=MYDB;uid=myuser;pwd=mypass].[SQLTable]

Pulls right from SQL Server into Access DB when run from the Access DB.

WATYFAuthor Commented:
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).

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

    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.
WATYFAuthor Commented:
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...


    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)

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.
WATYFAuthor Commented:
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... 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?

Alan WarrenApplications DeveloperCommented:

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
  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
  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


WATYFAuthor Commented:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

  '/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
          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)
              strLine = strLine & "," & rst.Fields(i)
            End If 'If Len(strLine) = 0

        Next i

      Print #lngFile, strLine
      strLine = vbNullString



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


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

  Resume Exit_WriteToTable

End Function

No objections.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.