id="info"

Question

Locked

Retive Data from an Access Query with Paramiters to Excel

By raul.patel ·
I am new to this so really sorry in advance.
I have manged to write the following code to copy data form an access query. Because I need now need to split the information, I now need add specific quires to it to be able to split the information I need to various work books.

This is the code so far?.
Sub Import_Data1()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stConn As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Maths_Data")
Set ws3 = ThisWorkbook.Worksheets("Front_Sheet")

stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db1_loc & ";"

With cn
.CursorLocation = adUseClient
.Open stConn
End With

Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn
cmd.CommandText = "Data_Packs_Back_Upload"

Set prm = cmd.CreateParameter
prm.Name = "Name1"
prm.Type = adVarChar
prm.Direction = adParamInput
prm.Size = 10
?This is the issue
prm.Value = ws2.Cells(2, 4)
cmd.Parameters.Append

With rs
.Open cmd
End With


ws1.Cells(2, 1).CopyFromRecordset rs

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
The Problem is that it dose not want to pick up the cell value as the parameter. I can get it to work for a date value, but this is a text value (First and Sure Name).
I have tried SQL statements but it was getting messy as I also need to keep active quires in access for other reports and this seamed the best option.

Any help would be gratefully appreciated.
Many Thanks
Rahul Patel

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Back to Software Forum
2 total posts (Page 1 of 1)  

Software Forums