SQL to access database...

[help_questions]

Does anyone have any code that refrences data from an Access database.
I want to populate the values of a report using SQL queries in VBA that reference a Access database. For example, I want to query Column A for segment 1 and input the result into report.xls, in cell b2. and so on.....i need some sample code to figure out, understand and modify to do my report.

can anyone help?

 

[acw]

Hi

Have a look at

http://www.mrexcel.com/board2/viewtopic.php?t=238246

There are quite a few examples on the board that show how to connect / extract from an access database. Do a search and see how you go.

For the example above, you have to set the Microsoft DAO x.x object library in your references.


Tony

 

[help_questions]

i seem to have a start...but am stuck on how to create a SQL statement

How to i say the following?
where field name = A and areacode = 123
where field name <> A and areacode = 123
where field name = A and areacode <> 123 and list=3343

and so on....hopefully, the response to this post will get me on my way.

 

[Darren Bartrup]

Write your queries in Access, then look at the SQL structure of them. Most can be copied directly over to Excel.

A couple of (not tested) examples for a select query and an action query:

    Dim rst As New ADODB.Recordset
    
    With rst
        .Open "SELECT Table1.A, Table1.AreaCode, Table1.List " & _
              "FROM Table1 " & _
              "WHERE (((Table1.AreaCode)=123) AND ((Table1.List)=54564));", _
              conn, adOpenKeyset, adLockOptimistic, adCmdText
        .MoveLast
        .MoveFirst
        NumberOfRecords = .RecordCount
        Do While Not .EOF
            ValueOfFieldA = .Fields(0)
            .MoveNext
        Loop
        .Close
    End With
    
    conn.Execute "DELETE Table1.* " & _
        "FROM Table1;", , adExecuteNoRecords