WATYF
asked on
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?
WATYF
P.S. I'm working in Access 2000
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
ASKER
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()).]In valid connection.
Here was the connection string I used:
con.Open "Provider=sqloledb;Data Source=C:\Temp\Test3.adp;I nitial 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
I tried using a SQL Server con string to connect to the ADP, but I kept getting this error:
[DBNETLIB][ConnectionOpen (ParseConnectParams()).]In
Here was the connection string I used:
con.Open "Provider=sqloledb;Data Source=C:\Temp\Test3.adp;I
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?
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 ...
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.
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.
ASKER
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
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.
ASKER
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
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
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
ASKER
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No objections.
Have a look here:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer