• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Excel ADOB connection to MSSql Sever and Sql error

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
JOKL4444
Asked:
JOKL4444
  • 6
  • 6
1 Solution
 
omgangIT ManagerCommented:
Yes.  Dates are indicated with # in Access but not in SQL Server.
OM Gang
0
 
omgangIT ManagerCommented:
Try changing # to a single apostrophe.
OM Gang
0
 
omgangIT ManagerCommented:
Example from SMS.  Criteria expression for a date query

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

OM Gang
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
JOKL4444Author Commented:
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
 
JOKL4444Author Commented:
Ok Just ran that and now it says invalid object name dbo_Security

Is there a problem with the CnnCNT?
0
 
omgangIT ManagerCommented:
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
 
JOKL4444Author Commented:
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
 
JOKL4444Author Commented:
I just increased the points as this has become a bit bigger
0
 
omgangIT ManagerCommented:
"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
 
JOKL4444Author Commented:
Nearly there - it has a problem with the Group presumably because I am using holding and not the case statement ?
0
 
omgangIT ManagerCommented:
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
 
JOKL4444Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now