Solved

Excel ADOB connection to MSSql Sever and Sql error

Posted on 2014-03-19
12
400 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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