[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel ADOB connection to MSSql Sever and Sql error

Posted on 2014-03-19
12
Medium Priority
?
405 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

650 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