Timing problem???

[Bill]

In the code below, I seem to be caught in some sort of
timing problem. Does the Currentdb.Execute need
some sort of additional parameters so that it completes
before the next record in rsAHSAddrlst is processed?
As it is, not all records processed are reflected in the
final output. Those that are came out fine, but there's
several missing.
***********************************************************************
Option Explicit
Public Sub AddAHS()
Dim con As ADODB.Connection
Dim rsAHSAddrlst As ADODB.Recordset
Dim strSQL As String

'==========================================================================================
'Open our temporary holding table for the AHS folks.
'==========================================================================================
Set con = Application.CurrentProject.Connection

Set rsAHSAddrlst = New ADODB.Recordset
rsAHSAddrlst.Open "SELECT * FROM [AHSAddrlst]", con, adOpenKeyset,
adLockOptimistic

While (Not (rsAHSAddrlst.EOF))

'==========================================================================================
'Okay, add the current individual to our general address list table.
'==========================================================================================
strSQL = "INSERT INTO [addrlst]
(LastName,FirstName,EmailHome,CityState,Hornet)"
strSQL = strSQL & " VALUES(" & Chr(34) & rsAHSAddrlst![LastName] &
Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![FirstName] & Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![EmailHome] & Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![CityState] & Chr(34) & ","
strSQL = strSQL & True & ");"

CurrentDb.Execute strSQL

rsAHSAddrlst.MoveNext
Wend

'Close the StatsKey recordset.
rsAHSAddrlst.Close
Set rsAHSAddrlst = Nothing

Set con = Nothing

End Sub

 

[Ken Snell \(MVP\)]

I would not expect to discover that you have a timing issue here.

Are you sure that the SQL statement is not causing an error for some of the
records, and that is why some are "missing"? Try using this step for the
execute step, as it will display an error if one is occuring:

CurrentDb.Execute strSQL, dbFailOnError

 

[Bill]

Ken,
I get a "Variable not defined when I add the dbFailOnError
to the execute statement. Do I need an additional reference
or something like that?
Thanks,
Bill

I would not expect to discover that you have a timing issue here.

Are you sure that the SQL statement is not causing an error for some of
the records, and that is why some are "missing"? Try using this step for
the execute step, as it will display an error if one is occuring:

CurrentDb.Execute strSQL, dbFailOnError


--

Ken Snell
<MS ACCESS MVP>

In the code below, I seem to be caught in some sort of
timing problem. Does the Currentdb.Execute need
some sort of additional parameters so that it completes
before the next record in rsAHSAddrlst is processed?
As it is, not all records processed are reflected in the
final output. Those that are came out fine, but there's
several missing.
***********************************************************************
Option Explicit
Public Sub AddAHS()
Dim con As ADODB.Connection
Dim rsAHSAddrlst As ADODB.Recordset
Dim strSQL As String

'==========================================================================================
'Open our temporary holding table for the AHS folks.
'==========================================================================================
Set con = Application.CurrentProject.Connection

Set rsAHSAddrlst = New ADODB.Recordset
rsAHSAddrlst.Open "SELECT * FROM [AHSAddrlst]", con, adOpenKeyset,
adLockOptimistic

While (Not (rsAHSAddrlst.EOF))

'==========================================================================================
'Okay, add the current individual to our general address list table.
'==========================================================================================
strSQL = "INSERT INTO [addrlst]
(LastName,FirstName,EmailHome,CityState,Hornet)"
strSQL = strSQL & " VALUES(" & Chr(34) & rsAHSAddrlst![LastName] &
Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![FirstName] & Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![EmailHome] & Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![CityState] & Chr(34) & ","
strSQL = strSQL & True & ");"

CurrentDb.Execute strSQL

rsAHSAddrlst.MoveNext
Wend

'Close the StatsKey recordset.
rsAHSAddrlst.Close
Set rsAHSAddrlst = Nothing

Set con = Nothing

End Sub

 

[Bill]

One of the values was a zero-length field which was
disallowed by the receivng table. So, SQL was
silently failing. I found that by analysis, as I was never
able to get the CurrentDb.Execute strSQL, dbFailOnError
to compile.

 

[Ken Snell \(MVP\)]

dbFailOnError is an intrinsic constant defined within ACCESS VBA itself. You
should not need another reference to have it work. Perhaps you have a
reference missing / messed up in the database file?
--

Ken Snell
<MS ACCESS MVP>

Ken,
I get a "Variable not defined when I add the dbFailOnError
to the execute statement. Do I need an additional reference
or something like that?
Thanks,
Bill

I would not expect to discover that you have a timing issue here.

Are you sure that the SQL statement is not causing an error for some of
the records, and that is why some are "missing"? Try using this step for
the execute step, as it will display an error if one is occuring:

CurrentDb.Execute strSQL, dbFailOnError


--

Ken Snell
<MS ACCESS MVP>

In the code below, I seem to be caught in some sort of
timing problem. Does the Currentdb.Execute need
some sort of additional parameters so that it completes
before the next record in rsAHSAddrlst is processed?
As it is, not all records processed are reflected in the
final output. Those that are came out fine, but there's
several missing.
***********************************************************************
Option Explicit
Public Sub AddAHS()
Dim con As ADODB.Connection
Dim rsAHSAddrlst As ADODB.Recordset
Dim strSQL As String

'==========================================================================================
'Open our temporary holding table for the AHS folks.
'==========================================================================================
Set con = Application.CurrentProject.Connection

Set rsAHSAddrlst = New ADODB.Recordset
rsAHSAddrlst.Open "SELECT * FROM [AHSAddrlst]", con, adOpenKeyset,
adLockOptimistic

While (Not (rsAHSAddrlst.EOF))

'==========================================================================================
'Okay, add the current individual to our general address list table.
'==========================================================================================
strSQL = "INSERT INTO [addrlst]
(LastName,FirstName,EmailHome,CityState,Hornet)"
strSQL = strSQL & " VALUES(" & Chr(34) & rsAHSAddrlst![LastName] &
Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![FirstName] & Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![EmailHome] & Chr(34) & ","
strSQL = strSQL & Chr(34) & rsAHSAddrlst![CityState] & Chr(34) & ","
strSQL = strSQL & True & ");"

CurrentDb.Execute strSQL

rsAHSAddrlst.MoveNext
Wend

'Close the StatsKey recordset.
rsAHSAddrlst.Close
Set rsAHSAddrlst = Nothing

Set con = Nothing

End Sub