Syntax???

[Mark A. Sam]

In the code below, I am adding specific records to SQL Server table linked
via ODBC to a remote website to by Access database.

Before I add a record, I check the SQL Table that it does not exist using
the DAO Findfirst method. This works fine when applying against the SQL
table directly but is slow, so I added a method to create a temporary local
table cloning the SQL data. This is represented by the recordset
rsLoadsSQLTemp. So before I add a record to the SQL table, I want to see if
it exists by checking against a local table cloning its data. This would
mean the the NoMatch property is True.

The problem is that the NoMatch property always returns False as if the
record exists in the local table. This isn't the case however. I can
delete all of the records from the local table (except one) and the NoMatch
property will still deliver a value of False. It would seem like a syntax
error, but I don't see how it could be. Something else is out of place.

Thanks for any help and God Bless,

Mark A. Sam




Private Sub Upload_Website_Click()
On Error GoTo error_Section

[txtMessage] = Null
DoEvents

DoCmd.SetWarnings False
DoCmd.OpenQuery "LoadsSQLTemp Maketable" 'Pull Web data to local computer to
speed FindFirst Method.
DoCmd.SetWarnings True

Dim rsLoads As Recordset 'Loads in ClientDB.mdb
Dim rsLoadsSQL As Recordset 'Loads on Website
Dim rsLoadsSQLTemp 'Speed up FindFirst Method
Dim strCriteriaSQL As String
Dim fld As Field
Dim i As Integer
Dim i2 As Integer

Set rsLoads = CurrentDb.OpenRecordset("LoadsSQLSource", dbOpenDynaset)
'Origin Table
Set rsLoadsSQL = CurrentDb.OpenRecordset("LoadsSQL", dbOpenDynaset,
dbSeeChanges) 'Destination Table
Set rsLoadsSQLTemp = CurrentDb.OpenRecordset("LoadsSQLTemp", dbOpenDynaset)

rsLoads.MoveFirst
Do Until rsLoads.EOF
If [tglStop] = True Then
[tglStop] = False
GoTo exit_Section
End If
strCriteriaSQL = "[CompanyID] = " & rsLoads![CompanyID] & _
" And [DateLoading] = #" & rsLoads![DateLoading] & "#" & _
" And [OriginCity] = '" & rsLoads![OriginCity] & "'" & _
" And [OriginState] = '" & rsLoads![OriginState] & "'" & _
" And [DestinationCity] = '" & rsLoads![DestinationCity] & "'"
& _
" And [DestinationState] = '" & rsLoads![DestinationState] &
"'" & _
" And [TrailerType] = '" & rsLoads![TrailerType] & "'"


rsLoadsSQLTemp.FindFirst strCriteriaSQL 'Check for existing load in table.
If rsLoadsSQL.NoMatch Then 'Add record becuase it doesn't exist.

 

[Graham Mandeno]

Hi Mark

You are performing the FindFirst on one recordset and checking NoMatch on
another:

rsLoadsSQLTemp.FindFirst strCriteriaSQL 'Check for existing load in
table.
If rsLoadsSQL.NoMatch Then 'Add record becuase it doesn't exist.

Also, you are declaring rsLoadsSQLTemp (implicitly) as a variant:

Dim rsLoadsSQLTemp 'Speed up FindFirst Method

I question the wisdom of bringing your entire SqlServer table across to
check for the existence of a single record. Would it not be better to write
a stored procedure in SqlServer and call it via a query?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

In the code below, I am adding specific records to SQL Server table linked
via ODBC to a remote website to by Access database.

Before I add a record, I check the SQL Table that it does not exist using
the DAO Findfirst method. This works fine when applying against the SQL
table directly but is slow, so I added a method to create a temporary
local
table cloning the SQL data. This is represented by the recordset
rsLoadsSQLTemp. So before I add a record to the SQL table, I want to see
if
it exists by checking against a local table cloning its data. This would
mean the the NoMatch property is True.

The problem is that the NoMatch property always returns False as if the
record exists in the local table. This isn't the case however. I can
delete all of the records from the local table (except one) and the
NoMatch
property will still deliver a value of False. It would seem like a syntax
error, but I don't see how it could be. Something else is out of place.

Thanks for any help and God Bless,

Mark A. Sam




Private Sub Upload_Website_Click()
On Error GoTo error_Section

[txtMessage] = Null
DoEvents

DoCmd.SetWarnings False
DoCmd.OpenQuery "LoadsSQLTemp Maketable" 'Pull Web data to local computer
to
speed FindFirst Method.
DoCmd.SetWarnings True

Dim rsLoads As Recordset 'Loads in ClientDB.mdb
Dim rsLoadsSQL As Recordset 'Loads on Website
Dim rsLoadsSQLTemp 'Speed up FindFirst Method
Dim strCriteriaSQL As String
Dim fld As Field
Dim i As Integer
Dim i2 As Integer

Set rsLoads = CurrentDb.OpenRecordset("LoadsSQLSource", dbOpenDynaset)
'Origin Table
Set rsLoadsSQL = CurrentDb.OpenRecordset("LoadsSQL", dbOpenDynaset,
dbSeeChanges) 'Destination Table
Set rsLoadsSQLTemp = CurrentDb.OpenRecordset("LoadsSQLTemp",
dbOpenDynaset)

rsLoads.MoveFirst
Do Until rsLoads.EOF
If [tglStop] = True Then
[tglStop] = False
GoTo exit_Section
End If
strCriteriaSQL = "[CompanyID] = " & rsLoads![CompanyID] & _
" And [DateLoading] = #" & rsLoads![DateLoading] & "#" & _
" And [OriginCity] = '" & rsLoads![OriginCity] & "'" & _
" And [OriginState] = '" & rsLoads![OriginState] & "'" & _
" And [DestinationCity] = '" & rsLoads![DestinationCity] &
"'"
& _
" And [DestinationState] = '" & rsLoads![DestinationState] &
"'" & _
" And [TrailerType] = '" & rsLoads![TrailerType] & "'"


rsLoadsSQLTemp.FindFirst strCriteriaSQL 'Check for existing load in
table.
If rsLoadsSQL.NoMatch Then 'Add record becuase it doesn't exist.

 

[Mark A. Sam]

Oh duhhhhhhh. I'm rolling my eyes over that one...lol.

Thanks Graham.

God Bless,

Mark

Hi Mark

You are performing the FindFirst on one recordset and checking NoMatch on
another:

rsLoadsSQLTemp.FindFirst strCriteriaSQL 'Check for existing load in
table.
If rsLoadsSQL.NoMatch Then 'Add record becuase it doesn't exist.

Also, you are declaring rsLoadsSQLTemp (implicitly) as a variant:

Dim rsLoadsSQLTemp 'Speed up FindFirst Method

I question the wisdom of bringing your entire SqlServer table across to
check for the existence of a single record. Would it not be better to write
a stored procedure in SqlServer and call it via a query?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

In the code below, I am adding specific records to SQL Server table linked
via ODBC to a remote website to by Access database.

Before I add a record, I check the SQL Table that it does not exist using
the DAO Findfirst method. This works fine when applying against the SQL
table directly but is slow, so I added a method to create a temporary
local
table cloning the SQL data. This is represented by the recordset
rsLoadsSQLTemp. So before I add a record to the SQL table, I want to see
if
it exists by checking against a local table cloning its data. This would
mean the the NoMatch property is True.

The problem is that the NoMatch property always returns False as if the
record exists in the local table. This isn't the case however. I can
delete all of the records from the local table (except one) and the
NoMatch
property will still deliver a value of False. It would seem like a syntax
error, but I don't see how it could be. Something else is out of place.

Thanks for any help and God Bless,

Mark A. Sam




Private Sub Upload_Website_Click()
On Error GoTo error_Section

[txtMessage] = Null
DoEvents

DoCmd.SetWarnings False
DoCmd.OpenQuery "LoadsSQLTemp Maketable" 'Pull Web data to local computer
to
speed FindFirst Method.
DoCmd.SetWarnings True

Dim rsLoads As Recordset 'Loads in ClientDB.mdb
Dim rsLoadsSQL As Recordset 'Loads on Website
Dim rsLoadsSQLTemp 'Speed up FindFirst Method
Dim strCriteriaSQL As String
Dim fld As Field
Dim i As Integer
Dim i2 As Integer

Set rsLoads = CurrentDb.OpenRecordset("LoadsSQLSource", dbOpenDynaset)
'Origin Table
Set rsLoadsSQL = CurrentDb.OpenRecordset("LoadsSQL", dbOpenDynaset,
dbSeeChanges) 'Destination Table
Set rsLoadsSQLTemp = CurrentDb.OpenRecordset("LoadsSQLTemp",
dbOpenDynaset)

rsLoads.MoveFirst
Do Until rsLoads.EOF
If [tglStop] = True Then
[tglStop] = False
GoTo exit_Section
End If
strCriteriaSQL = "[CompanyID] = " & rsLoads![CompanyID] & _
" And [DateLoading] = #" & rsLoads![DateLoading] & "#" & _
" And [OriginCity] = '" & rsLoads![OriginCity] & "'" & _
" And [OriginState] = '" & rsLoads![OriginState] & "'" & _
" And [DestinationCity] = '" & rsLoads![DestinationCity] &
"'"
& _
" And [DestinationState] = '" & rsLoads![DestinationState] &
"'" & _
" And [TrailerType] = '" & rsLoads![TrailerType] & "'"


rsLoadsSQLTemp.FindFirst strCriteriaSQL 'Check for existing load in
table.
If rsLoadsSQL.NoMatch Then 'Add record becuase it doesn't exist.