?
Solved

Excel ADOB connection to MSSql Sever and Sql error

Posted on 2014-03-19
12
Medium Priority
?
402 Views
Last Modified: 2014-03-26
Hi the following SQL string work fine from access but when I run it from Excel it throws an error saying something is wrong with the syntax around "#"

When I view strSSQL in the immediate window and paste it into Access it works fine

is there a different syntax when running it directly on the MSSql server?

' Comments:
    ' Params  :
    ' Returns :
    ' Modified:
   
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd
   
    Dim cnnCnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim rst1 As ADODB.Recordset
    Dim cmd As ADODB.Command
   
    Dim strStCon As String 'SQL Connection string
    Dim strStProcName As String 'Stored Procedure name
    Dim intVReturn As Integer
    'Declare variables for Stored Procedure
    Dim datVEndDate As Date
    Dim Venddate As Date
    Dim lngVCompanyid As Long
    Dim strSSQL As String
    Dim strMyReturn As String
    Dim strFVflag As String
    Dim TargetRow As Range
    Dim Vprofit As Currency
    'Set ADODB requirements
    Set cnnCnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set rst1 = New ADODB.Recordset
    Set cmd = New ADODB.Command
    Dim SumRng As Range
    Dim Frmrng As Range
    Dim Vfound As Boolean
    'Define database connection string
    strStCon = "PROVIDER=SQLOLEDB;Server=ACC02;Database=ProsporDB;Uid=ABC;Pwd=XYXYXY;"
   
    'Open database connection
    cnnCnt.ConnectionString = strStCon
    cnnCnt.Open
    Vfound = False
   
    Venddate = (Worksheets("Settings").Range("d8").value)
    If monthid = 12 Then monthid = 0
    lngVCompanyid = Worksheets("Settings").Range("d5").value

    Sheets("Positions").Select
    lastrow = findLastRow(Sheets("Positions"))
   
    If Cells(PStartRow, PSecID) <> "" Then
        Range(Cells(PStartRow, PSecID), Cells(lastrow, PIssuer)).Select
        Selection.ClearContents
    End If
        strSSQL = "SELECT dbo_SECURITY.SECURITYCODE, dbo_SECURITY.SECDESC, Sum(dbo_TRADE.QTY) AS Holding FROM dbo_SECURITY LEFT JOIN dbo_TRADE ON dbo_SECURITY.SECURITYID = dbo_TRADE.SECURITYID "
        strSSQL = strSSQL & " WHERE dbo_TRADE.TRADEDT <= #" & Venddate & "#"
        strSSQL = strSSQL & " GROUP BY dbo_SECURITY.SECURITYCODE, dbo_SECURITY.SECDESC, dbo_SECURITY.SECURITYTYPECODE, dbo_TRADE.FUNDID HAVING dbo_TRADE.FUNDID = " & lngVCompanyid & " AND Sum(dbo_TRADE.QTY) >0;"
   
    rst.Open strSSQL, cnnCnt
    Base1 = Worksheets("TrialBalance").Cells(65536, 1).End(xlUp).Row + 1
   
    Worksheets("TrialBalance").Cells(Base1, 1).CopyFromRecordset rst
   
    Columns("A:E").Select
    Selection.ColumnWidth = 12
   
    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Sub
   
PROC_ERR:
    MsgBox Err.Description, vbCritical, "SecuritiesExtract"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd
   
    'TVCodeTools ErrorHandlerStart
0
Comment
Question by:JOKL4444
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39940259
Yes.  Dates are indicated with # in Access but not in SQL Server.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39940265
Try changing # to a single apostrophe.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39940275
Example from SMS.  Criteria expression for a date query

 WHERE [start_date] > '2013-12-31'

OM Gang
0
ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.

 

Author Comment

by:JOKL4444
ID: 39940308
Damm I didn't know that - am fairly good with Access / Excel integration but this is my first attempt at Excel MSSql integration. I will give it a go and revert - I am sure it will work !!
0
 

Author Comment

by:JOKL4444
ID: 39940318
Ok Just ran that and now it says invalid object name dbo_Security

Is there a problem with the CnnCNT?
0
 
LVL 28

Expert Comment

by:omgang
ID: 39940332
That error seems to indicate it can't find a table named dbo_Security in the database indicated.  Best guess is that's how the tables were named when you had them linked in an Access db.  In SSMS, they're probably named something like
dbo.Security ????
So a select statement should look something like
SELECT SECURITYCODE FROM dbo.Security

OM Gang
0
 

Author Comment

by:JOKL4444
ID: 39943153
Ok Got it thanks

I need to embed an If Statement in the SQL which SQL cannot do so having looked on the web I see I have to use a case statement which I have tried without success as follows:

strSSQL = "SELECT dbo.TRADE.FUNDID, dbo.SECURITY.QUOTESERVICEID, dbo.SECURITY.SECURITYCODE, dbo.SECURITY.SECDESC, "
         strSSQL = strSSQL & " dbo.TRADE.QTY As Holding CASE WHEN dbo.TRADE.aCTIONCODE ='S' Then dbo.TRADE.QTY = dbo.TRADE.QTY *-1 ELSE dbo.TRADE.QTY END, dbo.SECURITY.SECURITYTYPECODE"
         strSSQL = strSSQL & " WHERE dbo.TRADE.TRADEDT <= '" & Venddate & "' And dbo.SECURITY.QUOTESERVICEID = 2 AND dbo.TRADE.FUNDID)= " & lngVCompanyid & ";"
        strSSQL = strSSQL & " GROUP BY holding, dbo.SECURITY.SECURITYCODE, dbo.SECURITY.SECDESC, dbo.SECURITY.SECURITYTYPECODE, dbo.SECURITY.SECURITYID, dbo.SECURITY.SECURITYTYPECODE, dbo.SECURITY.Symbol, dbo.TRADE.FUNDID, dbo.TRADE.SECCURR, dbo.SECURITY.QUOTESERVICEID, dbo.TRADE.FUNDID "

Any Pointers?

Rather than have to rewrite these queries for SQL in the excel VBA Code - is there anyway I can get excel to run a query in access which then runs the query on the data on the SQL Server and returns it to excel - I tried it many years ago and failed as far as I can remember it was all to do with the parsing - which is similar to shat is happening here I guess.
0
 

Author Comment

by:JOKL4444
ID: 39943157
I just increased the points as this has become a bit bigger
0
 
LVL 28

Expert Comment

by:omgang
ID: 39943209
"I THINK" you need to make a minor change.
dbo.Trade.Qty is an existing column in the table, correct?

Try

        strSSQL = strSSQL & " " & Chr(34) & "Holding" & Chr(34) & " = CASE WHEN dbo.TRADE.aCTIONCODE ='S' Then dbo.TRADE.QTY *-1 ELSE dbo.TRADE.QTY END,


OM Gang
0
 

Author Comment

by:JOKL4444
ID: 39943267
Nearly there - it has a problem with the Group presumably because I am using holding and not the case statement ?
0
 
LVL 28

Accepted Solution

by:
omgang earned 1000 total points
ID: 39943298
You cannot use the aliased column name in your GroupBy clause.  You use the same expression in the GroupBy that you use for the alias.

OM Gang

Also - Yes, you should be able to execute Access queries from Excel using Ms Query but keep in mind you won't have access to any of the run-time variables that are present when executed from Access.
0
 

Author Closing Comment

by:JOKL4444
ID: 39956818
Ok sorry about that I got the SQL to work and how can use it as a template for moving forward, I forgot I had not closed this out and awarded the points.

Thanks

John
]
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

765 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