copy cell value from excel to access db query

Status
Not open for further replies.

joe979

New Member
Joined
Jan 5, 2015
Messages
2
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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).

Code:
'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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
Hi Arithos!

I Have a similar code and I'm running with an error i do not quite understand. Please Help. I am trying to set an instance were the user inputs panel name and filters the query for that panel specifically.

1738715089908.png
1738715140713.png


VBA Code:
Sub FilterAnalogInput_DB()

    'Path
    Dim strPath As String
    
    'Provider
    Dim strProv As String
    
    'Connection String
    Dim strCn As String
    
    'Connection
    Dim Cn As New Connection
                  
    'RecordSet for AI
    Dim rsQry_AI As New Recordset
    
    'SQL Query for AI
    Dim strQry_AI As String
    
    
    'Establish connection to Project DB. Looks at the filepath specified in cell B1 of Project_DB Sheet
    strPath = ActiveWorkbook.Sheets("PAGE").Range("B1").Text
    strProv = "Microsoft.ACE.OLEDB.12.0;"
    strCn = "Provider=" & strProv & "Data Source=" & _
    strPath

    'Connection Open
    Cn.Open strCn
    
    'ActiveWorkbook.Sheets("PAGE").Range("B5").Text
    
    'SQL Query to import Digital Input Tags
    strQry_AI = "SELECT Instruments.PLCPanel, Instruments.Address FROM Instruments WHERE (((Instruments.PLCPanel) = [Panel Name Input]) And ((Instruments.AnalogInput) = True)) ORDER BY Instruments.PLCPanel, Instruments.Address;"
    
    rsQry_AI.Open strQry_AI, Cn
    
    With strQry_AI
        .Parameter("[Panel Name Input]") = Sheets("PAGE").Range("B5").Value
    End With
    
    
    'Puts Data into the Device Column of the Digital Device Sheet
    ActiveWorkbook.Sheets("AnalogInput").Range("A17").CopyFromRecordset rsQry_AI
    
    Cn.Close
    

End Sub
 
Upvote 0
I Have a similar code and I'm running with an error i do not quite understand. Please Help. I am trying to set an instance were the user inputs panel name and filters the query for that panel specifically.

Again:
Appears to be a duplicate to: Calling Data from access based on a specific name in TOC cell A:27

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

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
Back
Top