Solved

Connect to an ADP using ADO...

Posted on 2004-10-08
15
1,942 Views
Last Modified: 2007-10-18
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?



WATYF


P.S. I'm working in Access 2000
0
Comment
Question by:WATYF
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12262943
0
 
LVL 11

Author Comment

by:WATYF
ID: 12263139
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
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12263247
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?
0
 
LVL 84
ID: 12263273
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 ...
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12263321
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.
0
 
LVL 11

Author Comment

by:WATYF
ID: 12263413
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12263455
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.
0
 
LVL 11

Author Comment

by:WATYF
ID: 12264856
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12265001
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

0
 
LVL 11

Author Comment

by:WATYF
ID: 12265059
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
0
 
LVL 84
ID: 12266023
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


0
 
LVL 11

Accepted Solution

by:
WATYF earned 0 total points
ID: 12300298

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.



WATYF
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12309175
No objections.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now