copy cell value from excel to access db query

[joe979]

hi,
in my work place i got an access database which contains several queries.
each time a user need to run a certain query he must open the database, select the proper query and insert a value(i.e. date) and press run to get the data.
the main problem is that most users dont know how to open and run queries.
i have a decent knowledge in excel vb but never tried it with access.

is there a way to make an excel file which will have an input cell for the users to type and then will open the query, insert the value and run it?

also each time i open a query it requires me to enter a password, can i make the macro to enter the password instead of doing it manually?

*i dont want do edit the existing queries, just need to insert a value to a specific cell in the query.

thank you very much.

 

[Arithos]

Hi,

I dabbled with this awhile ago myself. When you have made sure you have all the correct "Tools->References" added, you can try this code, (or an abreviation suiting your needs).

'Step 1: Declare your variables
Dim MyDatabase As DAO.Database ' Just some setup for the work ahead
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
(ThisWorkbook.Path & "\DatabaseHB.accdb") 'I have my database in the same folder as this sheet, and u see my base. 

Set MyQueryDef = MyDatabase.QueryDefs("Settlement")  'What is your Query name? Insert it here, for me its "Settlement"

'Step 3: Define the Parameters
With MyQueryDef
   .Parameters("[Start]") = Sheets(1).Range("H3").Value  'I have two values, you can use one. Note that you will have to use the 'name from the access database query. for me its [Start], this is from the Criteria in the accessdb query.
   .Parameters("[End]") = Sheets(1).Range("H4").Value
End With


'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 Application.ScreenUpdating = False
 
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A2").CopyFromRecordset MyRecordset 'paste in A2, change to your liking
 
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name 'I use row 1 for headers given in the query
Next i

I hope this helps, and lets you get started.

Let me know if there are any issues.

 

[joe979]

hi arithos, thank you for the quick reply.I added DAO 3.5 from references, when im trying to run the code from excel I'm getting an activex component cannot create object error. when changed it to DAO 3.6 i got application defined error. am I missing anything?

 

[Arithos]

Hi,

You should add DAO 3.6

And you should be missing "Microsoft Access 12.0 Object Library" ( for excel 2007 ) Which I'm using. Add this aswell.

If you add this aswell it should be ok.

 

[alansidman]

this may be helpful also

Running an Access Parameter Query from Excel » Bacon Bits