query or SQL string

[Vsn]

Hi all,

In below code, would it be possible to change the recordset, by means of a
SQL statment in the string MySQL, with an query name?

'Data export section
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim fldCurr As DAO.Field
Dim intCurrColumn As Integer

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(MySQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
intCurrColumn = 1
With objActiveWksh
For Each fldCurr In rsCurr.Fields
.Cells(1, intCurrColumn) = fldCurr.Name
intCurrColumn = intCurrColumn + 1
Next fldCurr
.Cells(2, 1).CopyFromRecordset rsCurr
End With
End If

I would like to make a routine which accepts a query or an SQL string.

Thanks for advice,
Ludovic

 

[Allen Browne]

Yes: you can use a query name.

It won't work if the query contains names that require the Expression
Service to resolve -- names such as:
[Forms].[Form1].[Text0]

 

[Vsn]

Sorry i do react a bit late, i have been out.

I have a remaining question, it (code below) works fine with the queries and
SQL stings, but I remain to have troubles to export MEMO type fields. I do
have a 'large' table and subsequently a query or sql-string that crasches
when i do include the memo field. The memo field is filled with all kind
text just pasted into it. What could be the reason? Are the sertain
characters in the memo field which cause this to happen?

Thx,
Ludovic

Yes: you can use a query name.

It won't work if the query contains names that require the Expression
Service to resolve -- names such as:
[Forms].[Form1].[Text0]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

In below code, would it be possible to change the recordset, by means of
a SQL statment in the string MySQL, with an query name?

'Data export section
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim fldCurr As DAO.Field
Dim intCurrColumn As Integer

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(MySQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
intCurrColumn = 1
With objActiveWksh
For Each fldCurr In rsCurr.Fields
.Cells(1, intCurrColumn) = fldCurr.Name
intCurrColumn = intCurrColumn + 1
Next fldCurr
.Cells(2, 1).CopyFromRecordset rsCurr
End With
End If

I would like to make a routine which accepts a query or an SQL string.

Thanks for advice,
Ludovic

 

[Allen Browne]

Not sure what might be causing the crash.

There are several issues with memos (particularly if you aggregate on them),
and many other things that can cause JET queries to crash such as:
http://allenbrowne.com/NoYesNo.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry i do react a bit late, i have been out.

I have a remaining question, it (code below) works fine with the queries
and SQL stings, but I remain to have troubles to export MEMO type fields.
I do have a 'large' table and subsequently a query or sql-string that
crasches when i do include the memo field. The memo field is filled with
all kind text just pasted into it. What could be the reason? Are the
sertain characters in the memo field which cause this to happen?

Thx,
Ludovic

Yes: you can use a query name.

It won't work if the query contains names that require the Expression
Service to resolve -- names such as:
[Forms].[Form1].[Text0]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

In below code, would it be possible to change the recordset, by means of
a SQL statment in the string MySQL, with an query name?

'Data export section
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim fldCurr As DAO.Field
Dim intCurrColumn As Integer

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(MySQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
intCurrColumn = 1
With objActiveWksh
For Each fldCurr In rsCurr.Fields
.Cells(1, intCurrColumn) = fldCurr.Name
intCurrColumn = intCurrColumn + 1
Next fldCurr
.Cells(2, 1).CopyFromRecordset rsCurr
End With
End If

I would like to make a routine which accepts a query or an SQL string.

Thanks for advice,
Ludovic