Questions

Retive Data from an Access Query with Paramiters to Excel

Tags:
+
0 Votes
Locked

Retive Data from an Access Query with Paramiters to Excel

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