Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query data in access (2003)

    I have managed to write a simple VBA expression in excel that exports data to Access, and then another VBA script retrieves data from a predefined access query as follows:

    Sub ExtractDataFromAQuery()
    Dim db As Database
    Dim rs As Recordset
    Dim i As Integer
    i = 1
    Dim vrtRSExtractor(1 To 1000, 1 To 4) As Variant

    ' Open a database.
    Set db = OpenDatabase("Documents and SettingsapergunasMy DocumentsBiblio.mdb")

    Set rs = db.OpenRecordset("Query1")

    rs.MoveFirst
    With rs
    Do While Not rs.EOF
    vrtRSExtractor(i, 1) = !Name
    vrtRSExtractor(i, 2) = !Address1
    vrtRSExtractor(i, 3) = !Address2
    vrtRSExtractor(i, 4) = !Price
    .MoveNext
    i = i + 1
    Loop
    End With
    db.Close
    i = 1
    ThisWorkbook.Worksheets("Sheet2").Cells.Clear
    While vrtRSExtractor(i, 1) <> ""
    ThisWorkbook.Worksheets("Sheet2").Cells(i, 1).Value = vrtRSExtractor(i, 1)
    ThisWorkbook.Worksheets("Sheet2").Cells(i, 2).Value = vrtRSExtractor(i, 2)
    ThisWorkbook.Worksheets("Sheet2").Cells(i, 3).Value = vrtRSExtractor(i, 3)
    ThisWorkbook.Worksheets("Sheet2").Cells(i, 4).Value = vrtRSExtractor(i, 4)
    i = i + 1
    Wend

    End Sub

    Currently the above is associated with the access query:

    SELECT Table1.Test1, Table1.Name, Table1.Address1, Table1.Address2, Table1.Price
    FROM Table1
    WHERE (((Table1.Price)>20));

    Can somebody please explain how I can introduce a variable into the excel VBA script that can be passed to the access query so that I can vary the query criteria that is currently set at 20, from within excel.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query data in access (2003)

    1) Instead of opening a recordset on an Access query, you can use a SQL statement and introduce a variable element in it:

    Dim strSQL As String
    Dim dblLimit As Double
    dblLimit = Worksheets("Settings").Range("A2")
    strSQL = "SELECT Name, Address1, Address2, Price FROM Table1 WHERE Price > " & dblLimit
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    2) Instead of filling an array from the recordset, then filling cells from the array, you can use the CopyFromRecordset method:

    Worksheets("Sheet2").Range("A1").CopyFromRecordset rs

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query data in access (2003)

    Hans

    Thanks very much, that has made my day.

    As a supplementary question is there an SQL equivalent of the the following update a table statement.

    In the revised version I would like to use variables rather than the strings "Peter" and "Beck" etc

    Sub InsertDataIntoATable()
    Dim dbsExample As Database
    Dim rstExample As Recordset
    Dim fldExample As Field
    Dim rst As Recordset

    Dim dbs As Database, tdf As TableDef, fld As Field
    ' Open a database.
    Set dbs = OpenDatabase("Documents and SettingsapergunasMy DocumentsBiblio.mdb")

    Set rst = dbs.OpenRecordset("Authors")

    With rst
    .AddNew
    !FirstName = "Peter"
    !LastName = "Beck"
    !Phone = "0201 1234"
    !Notes = "This might work"
    .Update
    End With

    dbs.Close
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query data in access (2003)

    You could use the SQL for an append query:
    <code>
    Dim dbsExample As DAO.Database
    Dim strFirst As String
    Dim strLast As String
    Dim strPhone As String
    Dim strNotes As String
    Dim strSQL As String

    Set dbsExample = OpenDatabase("Documents and SettingsapergunasMy DocumentsBiblio.mdb")
    strFirst = Range("A1")
    strLast = Range("B1")
    strPhone = Range("A2")
    strNotes = Range("A3")
    strSQL = "INSERT INTO Authors (FirstName, LastName, Phone, Notes) " & _
    "VALUES ('" & strFirst & "', '" & strLast & "', '" & _
    strPhone & "', '" & strNotes & "')"
    dbsExample.Execute strSQL, dbFailOnError
    dbsExample.Close
    Set dbsExample = Nothing</code>

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query data in access (2003)

    Hans

    Thanks for all your help with this

    Eveything was going well until I wanted to create a query with a WHERE = clause that used strings.

    Can you please advise how to concatenate string variables, specially ones with spaces and strings which comprise account code numbers onto the end of the following line within the code already discussed.

    strSQL = "SELECT * FROM AccountsJournals WHERE Z00CodeFinal = & strAccountString

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query data in access (2003)

    String values must be enclosed in single or double quotes. For example:

    strSQL = "SELECT * FROM AccountsJournals WHERE Z00CodeFinal = " & Chr(34) & strAccountString & Chr(34)

    Chr(34) is the code for the double quote <code>"</code>.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query data in access (2003)

    Hans

    I thought that I had the retrieving of data methodology sorted out until I tried to run the following code this morning.

    Sub RetrieveDataFromCardBox()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String

    Set db = OpenDatabase("Documents and SettingsapergunasMy DocumentsMy KPMGMacrosMicrosoft AccessCardboxCardBox2.mdb")


    strSQL = "SELECT * FROM CardBox where ReqNo = 'ReqTest1'"

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    ThisWorkbook.Worksheets("Data Retrieved").Rows("2:65000").Clear
    ThisWorkbook.Worksheets("Data Retrieved").Range("A2").CopyFromRecordset rs

    db.Close

    End Sub

    Can you please explain why I might get a type miss-match error message at the set rs = db.... line, where this same line works with other test databases that I have constructed

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query data in access (2003)

    It might be a references problem. Make sure that you have set a reference to the Microsoft DAO 3.6 Object Library (in Tools | References...) and declare db and rs explicitly as DAO objects:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Does the problem go away if you do that?

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query data in access (2003)

    Adding in DAO did it!

Posting Permissions

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