0

Can you please help with access database queries from excel vba

My algorithm is as follows:

Open connection to access database

Select table1.column1, table1.column2, table1.column3 where table1.column3.value = “x” and table1.column2.value = “y”

Switch Case 1: If no records found matching the criteria from access database

Insert some values etc…

Switch Case 2 : If records found matching the criteria from access database

No action needed

Switch Case 3 : If records found not matching the criteria values from access database

Should display the values in an datagridview in excel vba

Should not allow to insert values

Kindly help me with the code needed from excel vba

Thanks Prabu M

Community
  • 1
  • 1
Prabu Mike
  • 119
  • 3
  • 13
  • Search SO for `[ms-access] [excel] ado` – Fionnuala Feb 16 '12 at 23:17
  • See this link. It will give you the basic idea on how to proceed. http://stackoverflow.com/questions/9083232/writing-excel-vba-to-receive-data-from-access/9085127#9085127 – Siddharth Rout Feb 17 '12 at 01:43
  • thanks dude but can you help me how to use switch case based on no records found from access DB or records not matching the criteria – Prabu Mike Feb 17 '12 at 16:51
  • @Prabu Mike: Strange, I missed this question. Would request you to use "@" and my name as I have done for you when replying so that I get an intimation that you replied :) Are you still looking for a solution? – Siddharth Rout Feb 28 '12 at 21:44

1 Answers1

0

note that the case3 an case1 are similar

you can use this code 

    Dim recSet As Recordset
    Set recSet = CurrentDb.OpenRecordset("Select table1.column1, table1.column2,"_ 
           & "table1.column3 where table1.column3.value = 'x' and table1.column2.value = 'y'")
    If recSet.EOF Then 'No records matching criteria
        'Do something
    Else 'There was records found
        'Do Something else
    End If
Ould Abba
  • 813
  • 1
  • 12
  • 25