Solved

Excel ADOB connection to MSSql Sever and Sql error

Posted on 2014-03-19
12
391 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 250 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now