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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -