exporting from a query

E

Eric Frohmann

Hi there.

What do I gotta do to export from a query into a text file??? When I run the
code below, I receive the following error:
"Operation is not allowed when the object is closed". This occurs when
testing if rst.RecordCount > 1. I've played with several different
permutations.
When I do a similar thing using "DoCmd.RunSQL stSql", I get a prompt
confirming I want to add records; plus how do I know if there are any
records???

On Error Resume Next
DoCmd.DeleteObject acTable, "prgTblExportStandData"
On Error GoTo Err_cfUploadData
stSql = "SELECT mnrcode, wg, type, age, ht, stkg, site, spc, fu, habu,
ecosite, survdate" _
& " INTO prgTblExportStandData FROM tblStand WHERE ((pkFlag=Yes)
AND (dtModifiedDate > #" & dtStartDate & "#));"
rst.Open stSql, cnn, adOpenStatic
If (rst.RecordCount > 1) Then
stTargetFile = emSaveAsFile("csv", "H:\")
If ((Not IsNull(stTargetFile)) And stTargetFile <> "") Then
DoCmd.TransferText acExportDelim, , "prgTblExportStandData",
stTargetFile
bSuccess = True
End If
Else
MsgBox "There is no data newer than: " & str(dtStartDate), vbOKOnly,
"No Data"
bSuccess = True
End If


TIA
 
G

Glen Appleton

Hi Eric,

First of all, when using the SELECT INTO SQL command, you are not opening an
accessible recordset. This is basically a make table query and will only
act as such. If you need to verify the recordcount, first open the
recordset with a SELECT statement, check the record count, close it, and
then run your make table sql command.

Hope this helps,
- Glen
 
E

Eric Frohmann

Hi Glen - Thanks for the reply.

Alright - trying it that way - I keep getting "Syntax error in FROM clause" -
I've whittled the SQL statement down to "Select * from tblStand;" - I've
tried creating a new connection to the currentproject, etc. etc. etc. - it
won't let go of this <grrrrrrrrr>

Any ideas???

On Error Resume Next
DoCmd.DeleteObject acTable, "prgTblExportStandData"
On Error GoTo Err_cfUploadData
stSqlSelect = "SELECT *"
stSqlEndSelect = " FROM tblStand;"
stSql = stSqlSelect & stSqlEndSelect
rst.Open stSql, cnn, , , adCmdTable >>>>> BOOM baby!!!!!
 
E

Eric Frohmann

If I change the one line to:
rst.Open stSql, cnn

It won't generate an error - but it also doesn't return with any records -
even there are records there....
 
E

Eric Frohmann

Anyone have any ideas???

--
--EricF

Eric Frohmann said:
Hi Glen - Thanks for the reply.

Alright - trying it that way - I keep getting "Syntax error in FROM clause" -
I've whittled the SQL statement down to "Select * from tblStand;" - I've
tried creating a new connection to the currentproject, etc. etc. etc. - it
won't let go of this <grrrrrrrrr>

Any ideas???

On Error Resume Next
DoCmd.DeleteObject acTable, "prgTblExportStandData"
On Error GoTo Err_cfUploadData
stSqlSelect = "SELECT *"
stSqlEndSelect = " FROM tblStand;"
stSql = stSqlSelect & stSqlEndSelect
rst.Open stSql, cnn, , , adCmdTable >>>>> BOOM baby!!!!!
Click to expand...
 
G

Glen Appleton

Hi Eric,

If you don't mind using the DAO methods, you can try this bit of code as an
example of checking if a table has records:

--- Begin Code ---
Dim rsTemp As DAO.Recordset
Dim strSQL As String
Dim blnHasRecs As Boolean

strSQL = "SELECT * FROM [MyTable];"
Set rsTemp = CurrentDb.OpenRecordset(strSQL)
blnHasRecs = Eval(rsTemp.RecordCount <> 0)
rsTemp.Close
Set rsTemp = Nothing
MsgBox blnHasRecs
--- End Code ---

Once you determined the recordset has records or not, you can then act on
them as needed.

Hope this helps,
- Glen
 
E

Eric Frohmann

Hi Glen.

I'm still baffled. I tried your code - it returns true, but only has 1
record!?!?!?!? I can run the SQL command interactively and it returns a
buncha records. Here's my test code - notice I'm establishing a whole new
connection. I noticed that the form itself had a filter applied - but things
still didn't work when I removed it (it shouldn't matter anyhow - right?)

--- Begin Code ---

Private Function cfUploadData(dtStartDate As Date) As Boolean
On Error GoTo Err_cfUploadData
Dim adoConn As New ADODB.Connection
Dim adoRs As New ADODB.Recordset
Dim bSuccess As Boolean
Dim stSql As String
Dim stSqlEndSelect As String
Dim stSqlSelect As String

Dim rsTemp As DAO.Recordset
Dim blnHasRecs As Boolean
Dim lNumRecs As Long

stSql = "SELECT * FROM [tblStand];"
Set rsTemp = CurrentDb.OpenRecordset(stSql)
blnHasRecs = Eval(rsTemp.RecordCount <> 0)
lNumRecs = rsTemp.RecordCount
rsTemp.Close
Set rsTemp = Nothing
MsgBox blnHasRecs 'returns TRUE
MsgBox lNumRecs 'returns 1

bSuccess = False
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Data\...\CruiserCollection.mdb"
On Error Resume Next
DoCmd.DeleteObject acTable, "prgTblExportStandData"
On Error GoTo Err_cfUploadData
stSqlSelect = "SELECT *"
stSqlEndSelect = " FROM tblStand;"
stSql = stSqlSelect & stSqlEndSelect

adoRs.Open stSql, adoConn
If (adoRs.RecordCount > 1) Then
'NEVER SEEMS TO GET TO HERE
Else
'ALWAYS GETS HERE THO
MsgBox "There is no data newer than: " & str(dtStartDate), vbOKOnly,
"No Data"
bSuccess = False
End If
..yadda....yadda..yadda..
--- End Code ---

Thanks again for your assistance.
 
E

Eric Frohmann

Hi Glen.

That worked - I now see the correct number of recs using the DAO methods.
Any idea why the ADO doesn't work???? I'm trying to keep everything in
ADO...

I used the same string for the query in ADO, so there really should be no
difference at all. I'll keep playing, but there must be something wrong with
the connection?, or is there a call I should be using rather than
RecordCount???

--
--EricF

Glen Appleton said:
Hi Eric,

In order to get the record count from the table, you first have to use the
.MoveLast method of the recordset. However, if you try to perform a
MoveLast on an empty recordset, it will cause an error. So, if you edit
your code like this, it should work:

--- Begin Code ---
Dim rsTemp As DAO.Recordset
Dim blnHasRecs As Boolean
Dim lNumRecs As Long

stSql = "SELECT * FROM [tblStand];"
Set rsTemp = CurrentDb.OpenRecordset(stSql)
blnHasRecs = Eval(rsTemp.RecordCount <> 0)
If blnHasRecs Then rsTemp.MoveLast ' ***
lNumRecs = rsTemp.RecordCount
rsTemp.Close
Set rsTemp = Nothing
MsgBox blnHasRecs 'returns TRUE
MsgBox lNumRecs 'returns 1
--- End Code ---

Hope this helps,
- Glen

Eric Frohmann said:
Hi Glen.

I'm still baffled. I tried your code - it returns true, but only has 1
record!?!?!?!? I can run the SQL command interactively and it returns a
buncha records. Here's my test code - notice I'm establishing a whole new
connection. I noticed that the form itself had a filter applied - but things
still didn't work when I removed it (it shouldn't matter anyhow - right?)

--- Begin Code ---

Private Function cfUploadData(dtStartDate As Date) As Boolean
On Error GoTo Err_cfUploadData
Dim adoConn As New ADODB.Connection
Dim adoRs As New ADODB.Recordset
Dim bSuccess As Boolean
Dim stSql As String
Dim stSqlEndSelect As String
Dim stSqlSelect As String

Dim rsTemp As DAO.Recordset
Dim blnHasRecs As Boolean
Dim lNumRecs As Long

stSql = "SELECT * FROM [tblStand];"
Set rsTemp = CurrentDb.OpenRecordset(stSql)
blnHasRecs = Eval(rsTemp.RecordCount <> 0)
lNumRecs = rsTemp.RecordCount
rsTemp.Close
Set rsTemp = Nothing
MsgBox blnHasRecs 'returns TRUE
MsgBox lNumRecs 'returns 1

bSuccess = False
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Data\...\CruiserCollection.mdb"
On Error Resume Next
DoCmd.DeleteObject acTable, "prgTblExportStandData"
On Error GoTo Err_cfUploadData
stSqlSelect = "SELECT *"
stSqlEndSelect = " FROM tblStand;"
stSql = stSqlSelect & stSqlEndSelect

adoRs.Open stSql, adoConn
If (adoRs.RecordCount > 1) Then
'NEVER SEEMS TO GET TO HERE
Else
'ALWAYS GETS HERE THO
MsgBox "There is no data newer than: " & str(dtStartDate), vbOKOnly,
"No Data"
bSuccess = False
End If
..yadda....yadda..yadda..
--- End Code ---

Thanks again for your assistance.
Click to expand...
Click to expand...
 
E

Eric Frohmann

Why would doing:
adoRs.Open stSql, adoConn, adOpenStatic, , adCmdTable
OR
adoRs.Open stSql, adoConn, adOpenStatic, adLockReadOnly, adCmdTable

suddenly create a sytax error in FROM clause???? This form is bound to the
table being queried (tblStand), but so what???
 
E

Eric Frohmann

Well - Shiver me Timbers matey!!! It's working now.

adoRs.Open stSql, adoConn, adOpenStatic

Could _someone_ pease enlighten me as to how the adCmdTable constant would so
radically change the behaviour of the command????

Glen: Thanks again for all the help!
 
Top