Pull data from Access

Hi All,

I have data about 1200 rows 40 Columns in Access. I need to pull this data in excel using VBA. So the user will just select dropdowns in excel (around 4-5 dropdown) .This would be the criteria based on which data should be pulled..

If its only criteria. I know how to pull data. But help needed when the user will have more than one criteria.


Thanks in advance.


Sujit

Originally Posted by pike

Hi sujitshukla
what code do you have sofar?

Hi,

this is the code i am using

Private Sub CommandButton1_Click()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim r As Long
con.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"data Source=C:\Documents and Settings\Administrator\Desktop\db1.mdb;"
rs.Open "tbl_name", con, adOpenKeyset, adLockOptimistic, adCmdTable
r = 6
Do While Len(Sheet1.Range("A" & r).Formula) > 0
With rs
.AddNew
.Fields("NAME") = Sheet1.Range("B" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing



End Sub

Originally Posted by pike

Hi sujitshukla
Can you please add code tags to your script as per the rules and it makes the code easier to read

Hi Pike..

Sorry about that, but do you have a solution for my query. I am still struggling.

Ok...Here it is sir

Please Login or Register  to view this content.

Now may i get something.

Hi
this is a code I use

Please Login or Register  to view this content.

You probably have to change it to fit your needs, but I hope it will help you.
Gunilla

Sorry this happens when you don't read the question to the end.
Gunilla

Thanks guys for the code...The code works perfectly fine.
My only query here is .I will provide the user with some options in excel sheet such as data validation.

So he will
Select a Start date in Cell A1 of Excel Sheet1.
Select a End date in Cell A2 of Excel Sheet1.
Select a Location in Cell A3 of Excel Sheet1.

My Query should pull data based on the above.

Logic wise i am clear . Putting this in VBA is kind of difficult for me.

Thanks once again

Hi Friends,

Need help from you all for the below mentioned Query.
When i am trying to extract data in excel from acess, I am getting a "Data Mismatch error". Can you suggest what is causing this.

There are 4 fields in access table Name (text), Date (date/time), Locatin(text) and Sr No.

Please Login or Register  to view this content.

Your help much appreciated.

Hi Tony,

Thanks for the prompt response. First thing is i am new to VBA . When i execute the query doing F8, the Select statement gets executed. the next line where i open the recordset i get an error there. I checked the format of cells in access and excel and it is the same. date format for date and text format for location.

Sujit