Solved

Syntax error in access 2000 module

Posted on 2004-08-09
4
191 Views
Last Modified: 2008-03-10
I am writing a module (VB for Applications) in Access 2000 and when I run the code I am getting a syntax error when the code tries to execute a Sql statement to pull data. The error comes on this line.
"rs.Open fbSql, strfbCon1, adOpenForwardOnly"

Runtime error
[Microsoft][ODBC SQL SERVER Driver][SQL Server]Line 1: Incorrect Syntax near 'master1'.

I have pasted the code and it runs with no errors in Microsoft SQLServer 7.0. Help!!

Code is Below.

Option Compare Database
Dim rs As ADODB.Recordset
Dim rsc As ADODB.Recordset 'Comments data
Dim rsol As ADODB.Recordset 'OLLW data
Dim res As ADODB.Recordset 'Resp Codes Exlcuded
Dim ollwSql As String
Dim fbSql As String
Dim lcCon As ADODB.Connection
Dim strfbCon As String
Dim strfbCon1 As String
Dim a As Long
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer
Dim g As Integer
Dim h As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim n As Integer
Dim x As Long
Dim acqn As Integer
Dim bkt As Integer
Dim iArray(4, 12, 19) As Integer
Dim dArray(12, 4) As Integer
Dim avcont As Integer  'Average Contact
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook

Sub mk_cfsb()

'strfbCon = "ODBC;Driver={Sql server};Server=Mtgsiadm070; Database=Daisy001LastMth;" & _
            "uid=bmugabe;pwd=daisy"
   
    'Refresh (strfbCon) main connection
strfbCon1 = "Driver={Sql server};Server=Mtgsiadm070; Database=Daisy001LastMth;" & _
            "uid=bmugabe;pwd=daisy"
           
            'Local connection
Set lcCon = New ADODB.Connection
        lcCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                    "Data Source =C:\Databases\Daisy.mdb; Persist Security Info=False"
   
   
    Set rs = New ADODB.Recordset  'Main recordset
    Set rsol = New ADODB.Recordset 'OLLW from Passport
    Set rsc = New ADODB.Recordset  'Comments table
    Set res = New ADODB.Recordset 'RespCodes Table data
   
fbSql = "SELECT master1.client, master1.loan_num, master1.next_due_date, master1.delq_payment_count, master1.man, master1.delq_class_code," & _
        "master1.pif_stop, master1.process_stop,bankruptcy.status_code AS bk_status_code,bankruptcy.chapter,forclosure.status_code AS fc_status_code," & _
        "loss_mitigation.status_code AS lm_status_code,reo_status.status AS reo_status, master1.prin_bal, master1.forcls_stop,DateDiff(d,client.process_date, master1.next_due_date)" & _
        "AS dlq_days, master1.investor,master4.sale_id,master3.tel_num,master3.sec_tel_num " & _
        "FROM master1 " & _
        " LEFT JOIN master3  ON master1.client = master3.client AND master1.loan_num = master3.loan_num" & _
        "LEFT JOIN master4  ON master1.client = master4.client AND master1.loan_num = master4.loan_num" & _
        "LEFT JOIN bankruptcy  ON master1.client = bankruptcy.client AND master1.loan_num = bankruptcy.loan_num" & _
        "LEFT JOIN foreclosure  ON master1.client = foreclosure.client AND master1.loan_num = foreclosure.loan_num" & _
        "LEFT JOIN loss_mitigation  ON master1.client = loss_mitigation.client AND master1.loan_num = loss_mitigation.loan_num" & _
        "LEFT JOIN reo_status  ON master1.client = reo_status.client AND master1.loan_num = reo_status.loan_num" & _
        "LEFT JOIN client  ON master1.client = client.client" & _
        "WHERE master1.client = '106' AND master1.pif_stop = '0' AND master1.prin_bal>0 AND (master1.investor BETWEEN 300 AND 399 OR " & _
        "master1.investor BETWEEN 400 AND 499 OR master1.investor BETWEEN 500 AND 599 OR master1.investor BETWEEN 'H01' AND 'H40' OR " & _
        "master1.investor BETWEEN 'L01' AND 'L99')"

        'OLLW Query
ollwSql = "SELECT ollw.loan_number,ollw.investorID,ollw.acq_id,ollw.let_date,ollw.let_id " & _
            "FROM ollw"
        'Comments Query
comSql = "SELECT * FROM Comments WHERE comments.add_date BETWEEN 07/01/2004 AND 07/31/2004"
 

        'Open Recordsets
 
  rs.Open fbSql, strfbCon1, adOpenForwardOnly
  rsol.Open ollwSql, lcCon, adOpenForwardOnly, adLockReadOnly
  rsc.Open comSql, lcCon, adOpenForwardOnly
  res.Open "SELECT Resp_codes FROM Resp_Codes", lcCon, adOpenForwardOnly
0
Comment
Question by:bmugabe
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11754974
In this block:

        " LEFT JOIN master3  ON master1.client = master3.client AND master1.loan_num = master3.loan_num" & _
        "LEFT JOIN master4  ON master1.client = master4.client AND master1.loan_num = master4.loan_num" & _
        "LEFT JOIN bankruptcy  ON master1.client = bankruptcy.client AND master1.loan_num = bankruptcy.loan_num" & _
        "LEFT JOIN foreclosure  ON master1.client = foreclosure.client AND master1.loan_num = foreclosure.loan_num" & _
        "LEFT JOIN loss_mitigation  ON master1.client = loss_mitigation.client AND master1.loan_num = loss_mitigation.loan_num" & _
        "LEFT JOIN reo_status  ON master1.client = reo_status.client AND master1.loan_num = reo_status.loan_num" & _
        "LEFT JOIN client  ON master1.client = client.client" & _

You don't appear to have any spaces between the end of one like and the "LEFT JOIN" of the following line.
Maybe this is the problem.
0
 

Expert Comment

by:itmastero
ID: 11754975
I think the connection drivers you have used is not proper or the driver for access 2000 would be corrupt. Try using other connection and if that doesn't works then try reinstalling office 2000 which might solve your problem. I can see that this is a connection error only.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11755022
Oops, typo,  I mean't "end of one line"  not "end of one like".

In case i wasn't very clear, take the first two lines of the block as an example:

        " LEFT JOIN master3  ON master1.client = master3.client AND master1.loan_num = master3.loan_num" & _
        "LEFT JOIN master4  ON master1.client = master4.client AND master1.loan_num = master4.loan_num" & _

The way you have this written will end up with:

        " LEFT JOIN master3  ON master1.client = master3.client AND master1.loan_num = master3.loan_numLEFT JOIN master4  ON master1.client = master4.client AND master1.loan_num = master4.loan_num"

Rather than:

        " LEFT JOIN master3  ON master1.client = master3.client AND master1.loan_num = master3.loan_num LEFT JOIN master4  ON master1.client = master4.client AND master1.loan_num = master4.loan_num"

Note the space between "master3.loan_num" at the end of the first line and "LEFT JOIN" at the start of the second.
0
 
LVL 5

Accepted Solution

by:
Jag5x5 earned 250 total points
ID: 11755504
I agree with Carl  If it were a connection issue it would not know there is a Syntax error in the SQL String.

I would start by making the changes carl suggested.  If you still have a problem you should run the Query by itself outside of the code and see that it runs and the syntax is correct.

HTH
Jag5x5
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

706 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

16 Experts available now in Live!

Get 1:1 Help Now