Recordset too large?

[alex]

Recordset too large?

Hello,
Using Access ’03…

I have some code below that’s in a form module and opens a recordset
based on what’s on the form.

The code works very well, but I’m now running into problems with large
recordsets (> 1000 or so). I get an error on rst.MoveNext (after 1151
records have been processed—inserted into a table). The error is: ODBC
—call failed ‘3146’. Is there a limit to how many records can be
processed by a DAO recordset?

Code:
‘other sql strings above

Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
strCareOf = _
"SELECT 'CareOf’ as Source, " & _
"File_Number " & _
"FROM MasterFile " & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All " & _
"SELECT ‘CareOf' as Source, " & _
"File_Number " & _
"FROM MasterFile2" & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All "
'Debug.Print strCareOf
End If

Dim strSQL As String
strSQL = strTip & strNol & strPer & strMer & strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL

' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html

Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
"Insert Into " & conTable & " " _
& "Select " _
& " '" & strLName & "' as LName, " _
& " '" & strFName & "' as FName, " _
& " '" & strDOB & "' as DOB, " _
& " '" & rst![Source] & "' as Source, " _
& " '" & rst![File_Number] & "' as File_Number "

'Debug.Print rst![File_Number] 'test results of union query
CurrentDb.Execute (strInsert) 'insert results into table
rst.MoveNext
Loop
‘end of code

One thing to probably look at is the recordset type…I had dynaset, but
it made no difference. I do not need to manipulate the data, just
grab it and insert into a table.

Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.

Thanks for any help/advice
alex

 

[Jeff Boyce]

Alex

It would appear that your code is stepping through the recordset, one record
at a time.

Why?

Access offers set-level processing, via queries. Is there a chance that you
could do the appending on the entire set at once, rather than one by one?

More info, please...

Regards

Jeff Boyce
Micrsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Recordset too large?

Hello,
Using Access ’03…

I have some code below that’s in a form module and opens a recordset
based on what’s on the form.

The code works very well, but I’m now running into problems with large
recordsets (> 1000 or so). I get an error on rst.MoveNext (after 1151
records have been processed—inserted into a table). The error is: ODBC
—call failed ‘3146’. Is there a limit to how many records can be
processed by a DAO recordset?

Code:
‘other sql strings above

Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
strCareOf = _
"SELECT 'CareOf’ as Source, " & _
"File_Number " & _
"FROM MasterFile " & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All " & _
"SELECT ‘CareOf' as Source, " & _
"File_Number " & _
"FROM MasterFile2" & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All "
'Debug.Print strCareOf
End If

Dim strSQL As String
strSQL = strTip & strNol & strPer & strMer & strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL

' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html

Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
"Insert Into " & conTable & " " _
& "Select " _
& " '" & strLName & "' as LName, " _
& " '" & strFName & "' as FName, " _
& " '" & strDOB & "' as DOB, " _
& " '" & rst![Source] & "' as Source, " _
& " '" & rst![File_Number] & "' as File_Number "

'Debug.Print rst![File_Number] 'test results of union query
CurrentDb.Execute (strInsert) 'insert results into table
rst.MoveNext
Loop
‘end of code

One thing to probably look at is the recordset type…I had dynaset, but
it made no difference. I do not need to manipulate the data, just
grab it and insert into a table.

Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.

Thanks for any help/advice
alex

 

[alex]

Recordset too large?

Hello,
Using Access ’03…

I have some code below that’s in a form module and opens a recordset
based on what’s on the form.

The code works very well, but I’m now running into problems with large
recordsets (> 1000 or so).  I get an error on rst.MoveNext (after 1151
records have been processed—inserted into a table).  The error is: ODBC
—call failed ‘3146’.  Is there a limit to how many records can be
processed by a DAO recordset?

Code:
‘other sql strings above

Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
    strCareOf = _
    "SELECT 'CareOf’ as Source, " & _
    "File_Number " & _
    "FROM MasterFile " & _
    "WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
    "Union All " & _
    "SELECT ‘CareOf' as Source, " & _
    "File_Number " & _
    "FROM MasterFile2" & _
    "WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
    "Union All "
'Debug.Print strCareOf
End If

Dim strSQL As String
    strSQL = strTip & strNol & strPer & strMer & strCareOf
    strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL

' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html

Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
    "Insert Into " & conTable & " " _
    & "Select " _
    & " '" & strLName & "' as LName, " _
    & " '" & strFName & "' as FName, " _
    & " '" & strDOB & "' as DOB, " _
    & " '" & rst![Source] & "' as Source, " _
    & " '" & rst![File_Number] & "' as File_Number "

    'Debug.Print rst![File_Number] 'test results of union query
    CurrentDb.Execute (strInsert) 'insert results into table
    rst.MoveNext
Loop
‘end of code

One thing to probably look at is the recordset type…I had dynaset, but
it made no difference.  I do not need to manipulate the data, just
grab it and insert into a table.

Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.

Thanks for any help/advice
alex

....just wanted to provide an update as I work to fix this problem.
It seems my problem is related more to an '*' (wildcard) in the first
name field, rather than the number of records. I'm not sure why
though.

 

[alex]

Recordset too large?

Hello,
Using Access ’03…

I have some code below that’s in a form module and opens a recordset
based on what’s on the form.

The code works very well, but I’m now running into problems with large
recordsets (> 1000 or so).  I get an error on rst.MoveNext (after 1151
records have been processed—inserted into a table).  The error is: ODBC
—call failed ‘3146’.  Is there a limit to how many records can be
processed by a DAO recordset?

Code:
‘other sql strings above

Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
    strCareOf = _
    "SELECT 'CareOf’ as Source, " & _
    "File_Number " & _
    "FROM MasterFile " & _
    "WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
    "Union All " & _
    "SELECT ‘CareOf' as Source, " & _
    "File_Number " & _
    "FROM MasterFile2" & _
    "WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
    "Union All "
'Debug.Print strCareOf
End If

Dim strSQL As String
    strSQL = strTip & strNol & strPer & strMer & strCareOf
    strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL

' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html

Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
    "Insert Into " & conTable & " " _
    & "Select " _
    & " '" & strLName & "' as LName, " _
    & " '" & strFName & "' as FName, " _
    & " '" & strDOB & "' as DOB, " _
    & " '" & rst![Source] & "' as Source, " _
    & " '" & rst![File_Number] & "' as File_Number "

    'Debug.Print rst![File_Number] 'test results of union query
    CurrentDb.Execute (strInsert) 'insert results into table
    rst.MoveNext
Loop
‘end of code

One thing to probably look at is the recordset type…I had dynaset, but
it made no difference.  I do not need to manipulate the data, just
grab it and insert into a table.

Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.

Thanks for any help/advice
alex

...just wanted to provide an update as I work to fix this problem.
It seems my problem is related more to an '*' (wildcard) in the first
name field, rather than the number of records.  I'm not sure why
though.- Hide quoted text -

- Show quoted text -

Jeff,
Thanks for responding...
1. Yes, I could insert the recordset all at once, but I'm not sure
how else to write the code!
2. I think my problem (originally posted) is that the recordset/query
is timing out.

I'll take any advice you can give on how to write the code better.

So you're saying that I shouldn't insert into the table one record at
a time...am I correct?

Thanks,

 

[Jeff Boyce]

Yes, I am.

Use a query and do it all at once.

Jeff B.

Recordset too large?

Hello,
Using Access ’03…

I have some code below that’s in a form module and opens a recordset
based on what’s on the form.

The code works very well, but I’m now running into problems with large
recordsets (> 1000 or so). I get an error on rst.MoveNext (after 1151
records have been processed—inserted into a table). The error is: ODBC
—call failed ‘3146’. Is there a limit to how many records can be
processed by a DAO recordset?

Code:
‘other sql strings above

Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
strCareOf = _
"SELECT 'CareOf’ as Source, " & _
"File_Number " & _
"FROM MasterFile " & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All " & _
"SELECT ‘CareOf' as Source, " & _
"File_Number " & _
"FROM MasterFile2" & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All "
'Debug.Print strCareOf
End If

Dim strSQL As String
strSQL = strTip & strNol & strPer & strMer & strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL

' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html

Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
"Insert Into " & conTable & " " _
& "Select " _
& " '" & strLName & "' as LName, " _
& " '" & strFName & "' as FName, " _
& " '" & strDOB & "' as DOB, " _
& " '" & rst![Source] & "' as Source, " _
& " '" & rst![File_Number] & "' as File_Number "

'Debug.Print rst![File_Number] 'test results of union query
CurrentDb.Execute (strInsert) 'insert results into table
rst.MoveNext
Loop
‘end of code

One thing to probably look at is the recordset type…I had dynaset, but
it made no difference. I do not need to manipulate the data, just
grab it and insert into a table.

Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.

Thanks for any help/advice
alex

...just wanted to provide an update as I work to fix this problem.
It seems my problem is related more to an '*' (wildcard) in the first
name field, rather than the number of records. I'm not sure why
though.- Hide quoted text -

- Show quoted text -

Jeff,
Thanks for responding...
1. Yes, I could insert the recordset all at once, but I'm not sure
how else to write the code!
2. I think my problem (originally posted) is that the recordset/query
is timing out.

I'll take any advice you can give on how to write the code better.

So you're saying that I shouldn't insert into the table one record at
a time...am I correct?

Thanks,

 

[alex]

Yes, I am.

Use a query and do it all at once.

Jeff B.

Recordset too large?
Hello,
Using Access ’03…
I have some code below that’s in a form module and opens a recordset
based on what’s on the form.
The code works very well, but I’m now running into problems with large
recordsets (> 1000 or so). I get an error on rst.MoveNext (after 1151
records have been processed—inserted into a table). The error is: ODBC
—call failed ‘3146’. Is there a limit to how many records can be
processed by a DAO recordset?
Code:
‘other sql strings above
Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
strCareOf = _
"SELECT 'CareOf’ as Source, " & _
"File_Number " & _
"FROM MasterFile " & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All " & _
"SELECT ‘CareOf' as Source, " & _
"File_Number " & _
"FROM MasterFile2" & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All "
'Debug.Print strCareOf
End If
Dim strSQL As String
strSQL = strTip & strNol & strPer & strMer & strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL
' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html
Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
"Insert Into " & conTable & " " _
& "Select " _
& " '" & strLName & "' as LName, " _
& " '" & strFName & "' as FName, " _
& " '" & strDOB & "' as DOB, " _
& " '" & rst![Source] & "' as Source, " _
& " '" & rst![File_Number] & "' as File_Number "
'Debug.Print rst![File_Number] 'test results of union query
CurrentDb.Execute (strInsert) 'insert results into table
rst.MoveNext
Loop
‘end of code
One thing to probably look at is the recordset type…I had dynaset, but
it made no difference. I do not need to manipulate the data, just
grab it and insert into a table.
Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.
Thanks for any help/advice
alex

...just wanted to provide an update as I work to fix this problem.
It seems my problem is related more to an '*' (wildcard) in the first
name field, rather than the number of records. I'm not sure why
though.- Hide quoted text -

- Show quoted text -

Jeff,
Thanks for responding...
1.  Yes, I could insert the recordset all at once, but I'm not sure
how else to write the code!
2.  I think my problem (originally posted) is that the recordset/query
is timing out.

I'll take any advice you can give on how to write the code better.

So you're saying that I shouldn't insert into the table one record at
a time...am I correct?

Thanks,- Hide quoted text -

- Show quoted text -

Jeff,
What do you mean "Use a query"?

 

[alex]

Yes, I am.

Use a query and do it all at once.

Jeff B.

"alex" <[email protected]> wrote in message

Recordset too large?
Hello,
Using Access ’03…
I have some code below that’s in a form module and opens a recordset
based on what’s on the form.
The code works very well, but I’m now running into problems with large
recordsets (> 1000 or so). I get an error on rst.MoveNext (after 1151
records have been processed—inserted into a table). The error is:ODBC
—call failed ‘3146’. Is there a limit to how many records canbe
processed by a DAO recordset?
Code:
‘other sql strings above
Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
strCareOf = _
"SELECT 'CareOf’ as Source, " & _
"File_Number " & _
"FROM MasterFile " & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All " & _
"SELECT ‘CareOf' as Source, " & _
"File_Number " & _
"FROM MasterFile2" & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All "
'Debug.Print strCareOf
End If
Dim strSQL As String
strSQL = strTip & strNol & strPer & strMer & strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL
' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html
Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
"Insert Into " & conTable & " " _
& "Select " _
& " '" & strLName & "' as LName, " _
& " '" & strFName & "' as FName, " _
& " '" & strDOB & "' as DOB, " _
& " '" & rst![Source] & "' as Source, " _
& " '" & rst![File_Number] & "' as File_Number "
'Debug.Print rst![File_Number] 'test results of union query
CurrentDb.Execute (strInsert) 'insert results into table
rst.MoveNext
Loop
‘end of code
One thing to probably look at is the recordset type…I had dynaset, but
it made no difference. I do not need to manipulate the data, just
grab it and insert into a table.
Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.
Thanks for any help/advice
alex
...just wanted to provide an update as I work to fix this problem.
It seems my problem is related more to an '*' (wildcard) in the first
name field, rather than the number of records. I'm not sure why
though.- Hide quoted text -
- Show quoted text -

Jeff,
Thanks for responding...
1.  Yes, I could insert the recordset all at once, but I'm not sure
how else to write the code!
2.  I think my problem (originally posted) is that the recordset/query
is timing out.

I'll take any advice you can give on how to write the code better.

So you're saying that I shouldn't insert into the table one record at
a time...am I correct?

Thanks,- Hide quoted text -

- Show quoted text -

Jeff,
What do you mean "Use a query"?- Hide quoted text -

- Show quoted text -

Jeff,

I've been researching query def, thinking that's what you mean...
I've got this code:
Dim strSQL As String
strSQL = strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
Debug.Print strSQL

Set dbs = CurrentDb

strSQL = "INSERT INTO tblPersonSetUp_tmp ( Source, LName,
File_Number )" & strSQL

Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)

qdf.Execute
'end of code

The code works, but most of my strSQL are union queries and union
queries cannot be used with Insert (as you know). How can I use a
union query (union sql strings) with and Insert statement?

I appreciate the help,
alex

 

[John W. Vinson]

The code works, but most of my strSQL are union queries and union
queries cannot be used with Insert (as you know). How can I use a
union query (union sql strings) with and Insert statement?

Save the UNION query as a stored query (use the CreateQuerydef method), and
then base an append query on the UNION query; or use a Subquery:

INSERT INTO target (field, field, field)
SELECT (field, field, field)
FROM
(SELECT... <your union query>)

 

[Jeff Boyce]

Alex

If this were mine (so take the notions with salt ...), I'd ...

Create a new query in design view (this assumes you are working inside the
Access UI).

Change that query to SQL view to construct the necessary UNION query.

Create another new query, pointing to the UNION query as its source/table.

Change THAT query to an Append query.

Run that last query.

NOTE: any append/insert operation changes your database. Be sure you have
backup copy(ies) before you start!

(NOTE2: all bets are off if you are not working in the Access UI)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Yes, I am.

Use a query and do it all at once.

Jeff B.

"alex" <[email protected]> wrote in message

Recordset too large?
Hello,
Using Access ’03…
I have some code below that’s in a form module and opens a recordset
based on what’s on the form.
The code works very well, but I’m now running into problems with
large
recordsets (> 1000 or so). I get an error on rst.MoveNext (after
1151
records have been processed—inserted into a table). The error is:
ODBC
—call failed ‘3146’. Is there a limit to how many records can be
processed by a DAO recordset?
Code:
‘other sql strings above
Dim strCareOf As String 'care of query
If Me.ckCareOf = True Then
strCareOf = _
"SELECT 'CareOf’ as Source, " & _
"File_Number " & _
"FROM MasterFile " & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All " & _
"SELECT ‘CareOf' as Source, " & _
"File_Number " & _
"FROM MasterFile2" & _
"WHERE Care_Of Like """ & strFName & " " & strLName & """ " & _
"Union All "
'Debug.Print strCareOf
End If
Dim strSQL As String
strSQL = strTip & strNol & strPer & strMer & strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
'Debug.Print strSQL
' Open pointer to current database
Set dbs = CurrentDb()
' Create recordset based on SQL
Set rst = dbs.OpenRecordset(strSQL) ', dbOpenDynaset) 'http://
allenbrowne.com/ser-29.html
Do While Not rst.EOF 'loop through recordset
'sql string must be inside loop to refresh file number
Dim strInsert As String 'insert person LName, FName, and DOB
strInsert = _
"Insert Into " & conTable & " " _
& "Select " _
& " '" & strLName & "' as LName, " _
& " '" & strFName & "' as FName, " _
& " '" & strDOB & "' as DOB, " _
& " '" & rst![Source] & "' as Source, " _
& " '" & rst![File_Number] & "' as File_Number "
'Debug.Print rst![File_Number] 'test results of union query
CurrentDb.Execute (strInsert) 'insert results into table
rst.MoveNext
Loop
‘end of code
One thing to probably look at is the recordset type…I had dynaset,
but
it made no difference. I do not need to manipulate the data, just
grab it and insert into a table.
Also, the ‘data’ is coming/retrieved from a linked table on a sql
server back-end.
Thanks for any help/advice
alex
...just wanted to provide an update as I work to fix this problem.
It seems my problem is related more to an '*' (wildcard) in the first
name field, rather than the number of records. I'm not sure why
though.- Hide quoted text -
- Show quoted text -

Jeff,
Thanks for responding...
1. Yes, I could insert the recordset all at once, but I'm not sure
how else to write the code!
2. I think my problem (originally posted) is that the recordset/query
is timing out.

I'll take any advice you can give on how to write the code better.

So you're saying that I shouldn't insert into the table one record at
a time...am I correct?

Thanks,- Hide quoted text -

- Show quoted text -

Jeff,
What do you mean "Use a query"?- Hide quoted text -

- Show quoted text -

Jeff,

I've been researching query def, thinking that's what you mean...
I've got this code:
Dim strSQL As String
strSQL = strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
Debug.Print strSQL

Set dbs = CurrentDb

strSQL = "INSERT INTO tblPersonSetUp_tmp ( Source, LName,
File_Number )" & strSQL

Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)

qdf.Execute
'end of code

The code works, but most of my strSQL are union queries and union
queries cannot be used with Insert (as you know). How can I use a
union query (union sql strings) with and Insert statement?

I appreciate the help,
alex

 

[alex]

Save the UNION query as a stored query (use the CreateQuerydef method), and
then base an append query on the UNION query; or use a Subquery:

INSERT INTO target (field, field, field)
SELECT (field, field, field)
FROM
  (SELECT... <your union query>)

John,
Thanks for helping...
What do you think is faster?
Data is linked via sql server.
alex

 

[alex]

John,
Thanks for helping...
What do you think is faster?
Data is linked via sql server.
alex- Hide quoted text -

- Show quoted text -

Jeff,
That's exactly what i've done to test the application...my union
queries need to be in code, however, because I need to export the
class module to other db's and 10 union query objects will be a
pain...

This query/code works perfect, but I need to modify it to accept a
union query:

Dim strSQL As String
strSQL = strCareOf
strSQL = Left(strSQL, Len(Trim(strSQL)) - 9) 'remove 'Union All'
from end of string
Debug.Print strSQL

Set dbs = CurrentDb

strSQL = "INSERT INTO tblPersonSetUp_tmp ( Source, LName,
File_Number )" & strSQL

Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)

qdf.Execute

I need to figure out how to base strSQL off of a union query (most
likely another query def); can you point me in the right direction?

alex