Connection but no records writing VBA query to ret

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Connection but no records writing VBA query to ret

Author

Topic

sportsguy
Starting Member

39 Posts

Posted - 2012-12-05 : 15:19:08

The original Excel VBA code retrieved data from MS Access just fine.
I adjusted the connection string, and I am connected to SQL Server 2012, I think, no errors!
however the SQL query does not return any records, and so there must be a trick to writing SQL queries in VBA to return records.
Any suggestions on how to write the query to return field strDISTRICTS from table dbo.DISTRICTS?

Thanks in advance - sportsguy

Here is the code:


Option Explicit
Public Const MyConn As String = _
    "Provider=SQLNCLI11;" & _
    "SERVER=servername;DATABASE=databasename;" & _
    "Trusted_Connection=yes"

Public mySQL As String
     
Public Cn As ADODB.Connection
Public Rs As ADODB.Recordset
Public Rw As Long
Public Col As Long
Public c As Long
Public MyField As Object
Public Destination As Range
Public OperationID As Range
Public FADescription As Range


Public Sub Retrieve_Erosion(ByVal BeginPeriod As Long, ByVal EndPeriod As Long, ByVal FADescription As String)
On Error GoTo Err_Retrieve
     
strSQL = "SELECT dbo.DISTRICTS.strDistrict FROM dbo.DISTRICTS;"

Debug.Print "SQL Statement reads " & strSQL
     
'Clear Prior Data
    Sheets("DATA").Select
    Range("F8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Range("F8").Select
     
    Debug.Print "Retrieve Begin " & BeginPeriod
    Debug.Print "Retrieve End " & EndPeriod
    Debug.Print "Retrieve Family " & FADescription

Application.Calculation = xlCalculationManual
    
     'Set destination
    
    Set Destination = [DATA!F8]
     
     'Create RecordSet
    Set Rs = New ADODB.Recordset
    Rs.Open strSQL, MyConn, adOpenForwardOnly, adLockReadOnly, adCmdText

    Rw = Destination.Row
    Col = Destination.Column
    c = Col
    Do Until Rs.EOF
        For Each MyField In Rs.Fields
            Cells(Rw, c) = MyField
            c = c + 1
        Next MyField
        Rs.MoveNext
        Rw = Rw + 1
        c = Col
    Loop
    
    Set Rs = Nothing

    Application.CalculateFullRebuild
    Application.Calculation = xlCalculationAutomatic
    ThisWorkbook.Save
    MsgBox "Data Imported and Spreadsheet Saved!", vbExclamation, "MYCOMPANY Financial Planning and Analysis"
    

Exit_Retrieve:
    Exit Sub
    
Err_Retrieve:
    Debug.Print Err.Number & " - " & Err.Description
    Resume Exit_Retrieve


End Sub

MS Access 20 years, SQL hack

sportsguy
Starting Member

39 Posts

Posted - 2012-12-05 : 16:58:47

I connect, but the server can't understand the query.

strSQL = "SELECT * FROM [dbo].[DISTRICTS]"

-2147217865 - Invalid object name 'dbo.DISTRICTS'.
-2147217865 - Invalid object name 'dbo_DISTRICTS'.
-2147217865 - Invalid object name 'DISTRICTS'.

any one know how to write a query through VBA which SQLServer 2012 will understand?

ugh!

MS Access 20 years, SQL hack

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-05 : 17:10:26

Try including the database name in the query: DatabaseName.dbo.DISTRICTS

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sportsguy
Starting Member

39 Posts

Posted - 2012-12-07 : 07:40:19

Good morning tara

yep, tried that, still didn't work. . . i am not sure if its that the recordset isn't posting to excel or that the records aren't being returned at all. . .

however, the DSN connection string should handle that,

after not looking at it for a couple of days, i think that my connection and connection string isn't correct. . . .

back to the drawing board.

MS Access 20 years, SQL hack

sportsguy
Starting Member

39 Posts

Posted - 2012-12-07 : 14:06:27

Got it work, it was the connection string not being initialized!
For any Newbies, here is the VBA code and trix to retrieve SQLSERVER data to excel

Required Trix:
1) copy working sql code from mgmt studio, do not copy from MS Access
2) Excel 2007 excel VBA references are
microsoft ADO ext 6.0
microsoft ActiveX data objects 6.0


Option Explicit

Public rngPeriod As Byte
Public rngYear As Integer

Public Const myConn As String = "Provider=SQLNCLI11;" & _
    "SERVER=servername;DATABASE=databasename;Trusted_Connection=Yes;"

Public BeginPeriod As Long
Public strSQL As String
Public Cn As ADODB.Connection
Public Rs As ADODB.Recordset
Public Rw As Long
Public Col As Long
Public c As Long
Public MyField As Object
Public Destination As Range

Sub Retrieve_Finished(BeginPeriod As Long)
On Error GoTo Err_Retrieve_Finished

strSQL = "SELECT * FROM * WHERE * ORDER BY *"

     
    Application.Calculation = xlCalculationManual

'Clear prior data in range
    Sheets("FINISHED").Select
    Range("A8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Set Destination = [FINISHED!A8]

'Begin Connection and data retrieval    
    Set Rs = New ADODB.Recordset
    Set Cn = New ADODB.Connection
    Cn.Open myConn
    Rs.Open strSQL, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    Rw = Destination.Row
    Col = Destination.Column
    c = Col
    Do Until Rs.EOF
        For Each MyField In Rs.Fields
            Cells(Rw, c) = MyField
            c = c + 1
        Next MyField
        Rs.MoveNext
        Rw = Rw + 1
        c = Col
    Loop
    
    Rs.Close
    Cn.Close
    
    Set Rs = Nothing
    Set Cn = Nothing


    Application.CalculateFullRebuild
    Application.Calculation = xlCalculationAutomatic

    MsgBox "Finished Data Imported Retrieved!", vbExclamation, "MyCompanyName Financial Planning and Analysis"
    

Exit_Retrieve_Finished:
    Exit Sub
    
Err_Retrieve_Finished:
    Set Rs = Nothing
    Debug.Print Err.Number & " - " & Err.Description
    Resume Exit_Retrieve_Finished


End Sub

MS Access 20 years, SQL hack

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources