SQL to access database...

help_questions

Board Regular
Joined
Aug 22, 2005
Messages
215
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

help_questions

Board Regular
Joined
Aug 22, 2005
Messages
215
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
    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
 

Forum statistics

Threads
1,141,587
Messages
5,707,238
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top