Troubles doing Data Retrieval from SQL

[Sauron]

Hi All,

I thought this was going to be nice and straightforward but i
certainly isn't... for me anyway!

What I am trying to do is build a SQL query using the data entered int
certain cells pass it to SQL and write it to a table, unfortunately
just can't seem to make it work.

I have searched and searched for information and have come up wit
nothing and I am really really stuck. I would be very grateful for som
help!

Thanks,
Sau

Code Follows...

*-*-*-*-*

Dim Dno As String
Dim PatFn As String
Dim PatSn As String
Dim SC1 As String
Dim SC2 As String
Dim SC3 As String
Dim MnQry As String
Dim FinishOff As String
Dim Con As ADODB.Connection
Dim RS As ADODB.Recordset
Dim TCell As Range
Dim DOB As String
Dim Col As Integer

Dno = Worksheets("Query").Range("B5").Value
PatFn = Worksheets("Query").Range("B8").Value
PatSn = Worksheets("Query").Range("B11").Value
DOB = Worksheets("Query").Range("B14").Value
SC1 = Worksheets("Query").Range("I8").Value
SC2 = Worksheets("Query").Range("I11").Value
SC3 = Worksheets("Query").Range("I14").Value

'Con.Provider = "SQLOLEDB.1"
'Con.ConnectionString
"DATABASE=concorde;SERVER=ecpsrv;UID=sa;PWD=password;"

Set Con = New ADODB.Connection
Con.ConnectionString = "DSN=ECPSRV"

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.StatusBar = "Writing data from ECPSRV..."
End With

Set RS = New ADODB.Recordset
With RS
If Dno = Not Null Then
MnQry = MnQry & " AND _Patient.MedicalRecordNumber = " & _
Dno
End If

If PatFn = Not Null Then
MnQry = MnQry & " AND _Patient.FirstName"
End If

If PatSn = Not Null Then
MnQry = MnQry & "AND _Patient.BirthDate"
End If

FinishOff = "Select" & "FROM"
If MnQry <> "" Then
FinishOff = FinishOff & "WHERE" & Mid$(MnQry, 5)
End If
End With


For Col = 0 To RS.Fields.Count - 1
Worksheets("Query").Range("B22").Offset(0, Col).Value
RS.Fields(Col).Name

Range("B22").Offset(1, 0).CopyFromRecordset RS

Set RS = Nothing
Con.Close
Set Con = Nothing

Next
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With


End Sub

*-*-*-*-

 

[Stevie_mac]

You dont appear to be executing to return a RecordSet e.g.

Set rs = Con.Execute(sSql)

you just seem to be creating a new RecordSet then expect it to have your data in ???


Heres an example
Dim d As New ADODB.Connection
Dim rs As ADODB.Recordset

d.ConnectionString = "Data Source=localserver;User ID=sa;Password=pwd;"
d.Open
Set rs = d.Execute("select * from table1")
While rs.EOF = False
Debug.Print rs(0)
rs.MoveNext
Wend

'Close connections & clean up etc....

 

[Sauron]

Cheers for the response!

Sorry I should have been more descriptive, it's the first part I'
having the trouble with, the bit where it uses cell input and joins i
all together in a query. I've tried and tried but it doesen't seem t
work properly!?!

Thanks!
Sa

 

[Sauron]

Is there an easier way of doing dynamically built SQL queries? I can'
think of another way, would anyone be able to suggest anything?

Cheers,
Sa

 

[Sauron]

Anyone have any ideas on this?

Cheers,
Sa

 

[Stevie_mac]

You really need to be more specific

I mean like, I've done this in the past...

Build a SQL select Query from Items on Sheet1
Output results of Query to Sheet2

Dim s as String, f as Field, r as long, c as long
s = "SELECT " & Sheet1.Cells(1,1).Value & " FROM " & Sheet1.Cells(2,1).Value & " WHERE " & Sheet1.Cells(3,1).Value
Set rs = cn.Execute(s)
while Not rs.EOF
for each f in rs
Sheets2.Cell(r,c).Value = f.Value
c = c + 1
next
rs.MoveNext
r = r + 1
wend

NOTE: this example has no error handling & is not realy good practice (since Sheet1 might not exist!) but it should get
you started!

If this is not what you need, give a bit more detail - someone will show you the way!

Stevie_Mac...

See ya.

 

[Stevie_mac]

Man - thats no good! out clubbing last night! - Try this instead
(note to self: Check before posting)


Sub test()
Dim db As Database, rs As Recordset
Set db = Workspaces(0).OpenDatabase("C:\A_DataBase.mdb")
Dim s As String, f As Field, r As Long, c As Long
s = "SELECT " & Sheet1.Cells(1, 1).Value & " FROM " & Sheet1.Cells(2, 1).Value & " WHERE " & Sheet1.Cells(3,
1).Value
Set rs = db.OpenRecordset(s)
r = 1 ' <-- important!
While Not rs.EOF
c = 1 ' <-- important!
For Each f In rs.Fields
Sheet2.Cells(r, c).Value = f.Value
c = c + 1
Next
rs.MoveNext
r = r + 1
Wend
End Sub

You really need to be more specific

I mean like, I've done this in the past...

Build a SQL select Query from Items on Sheet1
Output results of Query to Sheet2

Dim s as String, f as Field, r as long, c as long
s = "SELECT " & Sheet1.Cells(1,1).Value & " FROM " & Sheet1.Cells(2,1).Value & " WHERE " & Sheet1.Cells(3,1).Value
Set rs = cn.Execute(s)
while Not rs.EOF
for each f in rs
Sheets2.Cell(r,c).Value = f.Value
c = c + 1
next
rs.MoveNext
r = r + 1
wend

NOTE: this example has no error handling & is not realy good practice (since Sheet1 might not exist!) but it should get
you started!

If this is not what you need, give a bit more detail - someone will show you the way!

Stevie_Mac...

See ya.

 

[Sauron]

Cheers man, really grateful!

The only problem is that other cells can be chosen instead that's why
broke it down into a series of IF statements because it wouldn't wor
if I used a static query, it needs to build based on which cells ar
typed into. Hope that's enough info!

Cheers Very Much!
Sa

 

[onedaywhen]

The usual approach is to use static SQL in a stored procedure on the
server side and get the client to pass parameter values.