SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 Connection but no records writing VBA query to ret
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sportsguy
Starting Member

USA
38 Posts

Posted - 12/05/2012 :  15:19:08  Show Profile  Reply with Quote
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

USA
38 Posts

Posted - 12/05/2012 :  16:58:47  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36674 Posts

Posted - 12/05/2012 :  17:10:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sportsguy
Starting Member

USA
38 Posts

Posted - 12/07/2012 :  07:40:19  Show Profile  Reply with Quote
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
Go to Top of Page

sportsguy
Starting Member

USA
38 Posts

Posted - 12/07/2012 :  14:06:27  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000