Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    9

    Unanswered: Write and Run Access Queries from Excel

    Please I need help! I have a table in Access 97 and I would like to run a macro to query this table and pull the data into Excel. The code itself was modified from one by Andy Wiggins.This should be a real easy macro to write, but I keep getting all kinds of runtime errors. This is the latest error:-

    Error 3075 : Syntax error, (missing operator) in query expression.

    I have tried everything...Any help will be appreciated!

    Here is my code:

    '' Originally Written : 19-Oct-1999 by Andy Wiggins - Byg Software Ltd

    Sub Selectfromtable2()

    Dim dbs As Database
    Dim rs As Recordset
    Dim Ws As Worksheet
    Dim Path As String
    On Error GoTo ErrorHandler

    ThisWorkbook.Activate
    '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    ''Open the database
    Path = "C:\Documents and Settings\testu\Desktop\Rtest.mdb"
    Set dbs = OpenDatabase(Path)

    Set rs = dbs.OpenRecordset("SELECT * " & _
    "FROM [Pretrial]" & _
    "WHERE [Pretrial].[Name] = 'John'" & _
    "AND [Pretrial].[Age]= 35" & _
    "ORDER BY [Pretrial].[Score];")

    For i = 0 To rs.Fields.Count - 1
    Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next

    'The next line will get the data from the recordset and copy it
    'into the Worksheet (Sheet1).

    Ws.Range("A2").CopyFromRecordset rs

    'This next code set will just select the data region and auto-fit
    'the columns
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit
    Range("A1").Select

    '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    lbTidy:
    dbs.Close
    Set dbs = Nothing
    Set rs = Nothing

    Exit Sub
    '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    ErrorHandler:

    vtMessage = "Table and data creation error"
    vtMessage = vtMessage & _
    Chr(10) & _
    Chr(10) & "Error Number: " & Err & _
    Chr(10) & "Error Description: " & Error()

    MsgBox vtMessage, vbInformation, ctByg
    Resume lbTidy
    End Sub

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Which line is throwing the error?

    Could it be the missing spaces in the SQL statement? I'm not sure how forgiving it is to omit spaces.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well spotted. There are a number of missing spaces in the statements, one of which will parse but should still be adjusted. +1 point for knowing which one is accepted by the parser?
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    mind you it could be somehting kanky n Excel.. as strictly speaking it an Excel / JET problem.. it has nothing what so ever to do with Access.

    for the record though I'd agree its almost certainly going to be the SQL that is the problem, (for now)

    [/code]Set rs = dbs.OpenRecordset("SELECT * FROM [Pretrial]" & _
    " WHERE [Pretrial].[Name] = 'John'" & _
    " AND [Pretrial].[Age]= 35" & _
    " ORDER BY [Pretrial].[Score];")[/code]

    stylewise I'suggest you put a leading space in things such as multiline SQL statements, as its marginally easier to spot in my books

    Id also suggest that when you are opening SQL statements, assign the SQL to a variable so that its easy to debug the SQL by repeating it back to the screen in a msgbox, or setting a break point in the code so I can see what the SQL engine is being requested to do, as opposed to what I think the code is doing

    eg
    [/code]dim strSQL as string
    strSQL = "SELECT * FROM [Pretrial]" & _
    " WHERE [Pretrial].[Name] = 'John'" & _
    " AND [Pretrial].[Age]= 35" & _
    " ORDER BY [Pretrial].[Score];"
    msbox strsql,vbinformation,"SQL statement is....."
    Set rs = dbs.OpenRecordset(strSQL)
    [/code]
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2008
    Posts
    9
    Thanks to all for the input. I made the changes with regards to the spaces inthe SQL statement and I get another error noW;

    Run-Time error '91': Object variable or With block variable not set

    Debugger is pointing to this line in the code:
    Ws.Cells(1, i + 1).Value = rs.Fields(i).Name

    When I change my view of the VBA screen to the "locals window"- these are the values :
    Ws = Nothing

    Under connection, the value for dbs = <opertion is not supported for this type of object>

    I am inclined to believe that my recordset is not being created in the first place.'

    I don't know what esle to do. I tried other forums and it seems that no one really has a clear cut working solution for querying access tables using VBA code.

    Any additional input will be appreciated. thanks!
    __________________________________________________ ___________
    Quote Originally Posted by healdem
    mind you it could be somehting kanky n Excel.. as strictly speaking it an Excel / JET problem.. it has nothing what so ever to do with Access.

    for the record though I'd agree its almost certainly going to be the SQL that is the problem, (for now)

    [/code]Set rs = dbs.OpenRecordset("SELECT * FROM [Pretrial]" & _
    " WHERE [Pretrial].[Name] = 'John'" & _
    " AND [Pretrial].[Age]= 35" & _
    " ORDER BY [Pretrial].[Score];")[/code]

    stylewise I'suggest you put a leading space in things such as multiline SQL statements, as its marginally easier to spot in my books

    Id also suggest that when you are opening SQL statements, assign the SQL to a variable so that its easy to debug the SQL by repeating it back to the screen in a msgbox, or setting a break point in the code so I can see what the SQL engine is being requested to do, as opposed to what I think the code is doing

    eg
    [/code]dim strSQL as string
    strSQL = "SELECT * FROM [Pretrial]" & _
    " WHERE [Pretrial].[Name] = 'John'" & _
    " AND [Pretrial].[Age]= 35" & _
    " ORDER BY [Pretrial].[Score];"
    msbox strsql,vbinformation,"SQL statement is....."
    Set rs = dbs.OpenRecordset(strSQL)
    [/code]

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well you are now into the realms of Excel not Access... so its probably best to ask this question in the Excel thread on this forum
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Seems to me that WS is never set.

    You probably need a line such as Set Ws = CurrentWorksheet or something. I have no idea of the syntax or specifics though since I'm an Access man
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just to dot the i's and cross t's

    I think it should be

    Set ws = ActiveSheet

    Also, if it is the active sheet to be written to (as is implied), then you can dispence with

    dim Ws
    Set ws = ActiveSheet

    and just use

    Cells(1, i + 1).Value = rs.Fields(i).Name

    Although when you have multiple workbook open it can be better (code easier to follow) if references are explicitly set the different wooks/sheets (this is preferable to activating the books/sheet as required).


    MTB

  9. #9
    Join Date
    May 2008
    Posts
    9

    Thanks!

    That was the problem! I can't believe I missed that . It's the little things that get us. Once I set Ws = activesheet, it worked like a miracle.

    Thank you!

    And Thanks to all others for their input!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •