I am writing some code that runs in a form to get records from a sql
database. The recordset comes back clean and has the records I expected and
the sheet gets the command to populate but then nothing happens.

When I kill the VBA form the sheet refreshes with the data. before I kill
it the form is in hung mode. when I drag it around it leaves a trail.

any ideas?

Nope that didnt work. Here is my routine

Private Sub btnLoadSheet_Click()
Application.ScreenUpdating = True

Call SetHeadings
If FormValid() = False Then GoTo LSExit

strDWFilePath = "Driver={SQL
Server};Server=TREMOLITE;Database=RDConv14_db_0827 2008;User Id=aris;"
Set cnnDW = New ADODB.Connection
Set rsDW = New ADODB.Recordset

Sheet1.Range("A2:P99").ClearContents

cnnDW.Open strDWFilePath

sQRY = "SELECT * " & _
"FROM adet x " & _
"WHERE x.license = '" & txtLicense.Text & "' " & _
IIf(txtSuffix.Text = "", "", "AND x.sf like '[" & txtSuffix.Text
& "]' ") & _
"AND x.wkpd between " & txtFromPd.Text & " and " & txtToPd.Text &
" " & _
"ORDER BY 1,2,3,x.wkpd"

rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("A2").CopyFromRecordset rsDW

rsDW.Close
Set rsDW = Nothing

cnnDW.Close
Set cnnDW = Nothing

Sheet1.Range("A2").Show
LSExit:

End Sub

Look at the ninth row up from bottom...

"Application.ScreenUpdating = False
Sheet1.Range("A2").CopyFromRecordset rsDW"
--
Jim Cone
Portland, Oregon USA



"Richard Douglass"

wrote in message
Nope that didnt work. Here is my routine

Private Sub btnLoadSheet_Click()
Application.ScreenUpdating = True

Call SetHeadings
If FormValid() = False Then GoTo LSExit

strDWFilePath = "Driver={SQL
Server};Server=TREMOLITE;Database=RDConv14_db_0827 2008;User Id=aris;"
Set cnnDW = New ADODB.Connection
Set rsDW = New ADODB.Recordset

Sheet1.Range("A2:P99").ClearContents

cnnDW.Open strDWFilePath

sQRY = "SELECT * " & _
"FROM adet x " & _
"WHERE x.license = '" & txtLicense.Text & "' " & _
IIf(txtSuffix.Text = "", "", "AND x.sf like '[" & txtSuffix.Text
& "]' ") & _
"AND x.wkpd between " & txtFromPd.Text & " and " & txtToPd.Text &
" " & _
"ORDER BY 1,2,3,x.wkpd"

rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("A2").CopyFromRecordset rsDW

rsDW.Close
Set rsDW = Nothing

cnnDW.Close
Set cnnDW = Nothing

Sheet1.Range("A2").Show
LSExit:

End Sub

Jim thank you very much.


That did it.


"Jim Cone" wrote in message
...
Look at the ninth row up from bottom...

"Application.ScreenUpdating = False
Sheet1.Range("A2").CopyFromRecordset rsDW"
--
Jim Cone
Portland, Oregon USA



"Richard Douglass"

wrote in message
Nope that didnt work. Here is my routine

Private Sub btnLoadSheet_Click()
Application.ScreenUpdating = True

Call SetHeadings
If FormValid() = False Then GoTo LSExit

strDWFilePath = "Driver={SQL
Server};Server=TREMOLITE;Database=RDConv14_db_0827 2008;User Id=aris;"
Set cnnDW = New ADODB.Connection
Set rsDW = New ADODB.Recordset

Sheet1.Range("A2:P99").ClearContents

cnnDW.Open strDWFilePath

sQRY = "SELECT * " & _
"FROM adet x " & _
"WHERE x.license = '" & txtLicense.Text & "' " & _
IIf(txtSuffix.Text = "", "", "AND x.sf like '[" & txtSuffix.Text
& "]' ") & _
"AND x.wkpd between " & txtFromPd.Text & " and " & txtToPd.Text
&
" " & _
"ORDER BY 1,2,3,x.wkpd"

rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("A2").CopyFromRecordset rsDW

rsDW.Close
Set rsDW = Nothing

cnnDW.Close
Set cnnDW = Nothing

Sheet1.Range("A2").Show
LSExit:

End Sub