Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compare Tables Code 1

Status
Not open for further replies.
lars7

lars7

Technical User
Aug 16, 2005
817
GB
Hi Guys,

I found this code to compare tables in my database and it works great untill it hits this records when I get an error message:


"syntax error (missing operator) in query expression ''O'Neill ');'."


Can anyone see where the problem is?

thanks in advance.

Sub CompareTables2(BaseTable As String, PrimaryKeyField As String, _
BaseTableQuery As String, VaryingTableQuery As String)
'parameters
' BaseTable: the table that is considered the 'base', that is, the one considered accurate
' PrimaryKeyField: the primary key for both tables
' qryBase: an ordered query based on the 'base' table
' qryVarying: an ordered query based on the table being compared to the 'base'

On Error GoTo Err_CompareTables
Dim db As dao.Database
Dim rstBase As dao.Recordset
Dim rstVarying As dao.Recordset
Dim tdf As dao.TableDef
Dim fld As dao.Field
Dim FieldChanged As Boolean
Dim ErrorMessage As String

Set db = CurrentDb

Set rstBase = db.OpenRecordset(BaseTableQuery)
Set rstVarying = db.OpenRecordset(VaryingTableQuery)
Set tdf = db.TableDefs(BaseTable)

db.TableDefs.Delete "TableDiscrepancies2"
db.Execute ("CREATE TABLE TableDiscrepancies2 (RecordNumber TEXT(255), FieldName TEXT(255), OldText TEXT(255), NewText TEXT(255));")

rstBase.MoveFirst
rstVarying.MoveFirst

Do Until rstBase.EOF
If rstVarying.EOF = True Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" deleted ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES) ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
FieldChanged = True
Next fld

rstBase.MoveNext
ElseIf rstBase(PrimaryKeyField) > rstVarying(PrimaryKeyField) Then
ErrorMessage = "**** record " & rstVarying(PrimaryKeyField) & _
" added ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, NewText)" _
& " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & rstVarying(fld.Name) & "');")
FieldChanged = True
Next fld

rstVarying.MoveNext
ElseIf rstBase(PrimaryKeyField) < rstVarying(PrimaryKeyField) Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" deleted ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
FieldChanged = True
Next fld

rstBase.MoveNext
Else
FieldChanged = False
For Each fld In tdf.Fields
If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then
If Not FieldChanged Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" Modified ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")
End If
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText, NewText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & Nz(rstBase(fld.Name), "<Null>") & "','" & Nz(rstVarying(fld.Name), "<Null>") & "');")
FieldChanged = True
End If
Next fld
' If Not FieldChanged Then
' ErrorMessage = "Record " & rstBase(PrimaryKeyField) & _
' " identical"
' End If
rstBase.MoveNext
rstVarying.MoveNext
FieldChanged = False
End If
Loop
Do Until rstVarying.EOF
ErrorMessage = "**** record " & rstVarying(PrimaryKeyField) & _
" added to ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, NewText)" _
& " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & rstVarying(fld.Name) & "');")
FieldChanged = True
Next fld

rstVarying.MoveNext
Loop

Exit_CompareTables:
Set rstBase = Nothing
Set rstVarying = Nothing
Debug.Print "Done."
db.Close
Exit Sub

Err_CompareTables:
If Err.Number = 3010 Then '*** if the error is the table is missing
Resume Next '*** then skip the delete line and resume on the next line
ElseIf Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_CompareTables
End If
Debug.Print "Done"
End Sub

 
Sort by date Sort by votes
lol

This has always been a problem, the apostrophe between o and neil is interpreted as a text delimiter. So the generated code is corrupt.

You know the quickest easiest fix? Go to your table and perform an edit replace and replace all apostrophe with ` symbol which on my keyboard is top left.

If you want the more elegant solution yu can adjust your code to cater for this with double apostrophes etc

JB
 
Upvote 0 Downvote
Hi JB ,

this table will be refreshed every week so I need a fix in the code if possible
 
Upvote 0 Downvote
Try this, sorry i don't have access to test:

Code: 
function SortAps(strIn as string) as string
SortAps=replace(strIn,"'","`")
end function

just add the function under the code above, that'll be fine.

and anywhere in your code you pass a name just prefix it with that function. Place to stat would be the line where you indentified it passing o'neil!

Let me know how you go

JB
 
Upvote 0 Downvote
Hi JB,

I tried your code here but I got an error message:

"Expected=;"


db.Execute SortAps(("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');"))

is this what you intended I do?
 
Upvote 0 Downvote
No mate. That would remove ALL the ' and you only want to remove them from the persons name. Disable error checking (put a ' in front on the line on error....) and let me know which line of code it bombs on. In fact, you'll probbly spot it yourself from that point. Find out whih variable contains "o'neil" by hovering you mouse over and just pass that variable to the function.


JB
 
Upvote 0 Downvote
Code: 
db.Execute "INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, NewText) VALUES ('" _
   & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & [!]Replace([/!]rstVarying(fld.Name)[!], "'", "''")[/!] & "');"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Hi Guys,
Sorry for not responding earlier,

PH,

I added you code and I got a slightly different error message:

"syntax error (missing operator) in query expression ''O'Neill '<NULL>');'.

I changed it in 2 places:


Sub CompareTables2(BaseTable As String, PrimaryKeyField As String, _
BaseTableQuery As String, VaryingTableQuery As String)
'parameters
' BaseTable: the table that is considered the 'base', that is, the one considered accurate
' PrimaryKeyField: the primary key for both tables
' qryBase: an ordered query based on the 'base' table
' qryVarying: an ordered query based on the table being compared to the 'base'

On Error GoTo Err_CompareTables
Dim db As DAO.Database
Dim rstBase As DAO.Recordset
Dim rstVarying As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim FieldChanged As Boolean
Dim ErrorMessage As String

Set db = CurrentDb

Set rstBase = db.OpenRecordset(BaseTableQuery)
Set rstVarying = db.OpenRecordset(VaryingTableQuery)
Set tdf = db.TableDefs(BaseTable)

db.TableDefs.Delete "TableDiscrepancies2"
db.Execute ("CREATE TABLE TableDiscrepancies2 (RecordNumber TEXT(255), FieldName TEXT(255), OldText TEXT(255), NewText TEXT(255));")

rstBase.MoveFirst
rstVarying.MoveFirst

Do Until rstBase.EOF
If rstVarying.EOF = True Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" deleted ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
FieldChanged = True
Next fld

rstBase.MoveNext
ElseIf rstBase(PrimaryKeyField) > rstVarying(PrimaryKeyField) Then
ErrorMessage = "**** record " & rstVarying(PrimaryKeyField) & _
" added ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, NewText)" _
& " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & [COLOR=red yellow]Replace(rstVarying(fld.Name), "'", "''")[/color] & "');")
FieldChanged = True
Next fld

rstVarying.MoveNext
ElseIf rstBase(PrimaryKeyField) < rstVarying(PrimaryKeyField) Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" deleted ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
FieldChanged = True
Next fld

rstBase.MoveNext
Else
FieldChanged = False
For Each fld In tdf.Fields
If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then
If Not FieldChanged Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" Modified ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")
End If
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText, NewText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & Nz(rstBase(fld.Name), "<Null>") & "','" & Nz(rstVarying(fld.Name), "<Null>") & "');")
FieldChanged = True
End If
Next fld
' If Not FieldChanged Then
' ErrorMessage = "Record " & rstBase(PrimaryKeyField) & _
' " identical"
' End If
rstBase.MoveNext
rstVarying.MoveNext
FieldChanged = False
End If
Loop
Do Until rstVarying.EOF
ErrorMessage = "**** record " & rstVarying(PrimaryKeyField) & _
" added to ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, NewText)" _
& " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & [COLOR=red yellow]Replace(rstVarying(fld.Name), "'", "''")[/color] & "');")
FieldChanged = True
Next fld
 
Upvote 0 Downvote
Seems you forgot the following (2 times):
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText, NewText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & Nz(rstBase(fld.Name), "<Null>") & "','" & Nz(rstVarying(fld.Name), "<Null>") & "');")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I can't seem to remember, if you use the double quote as the text qualifier doesn't it prevent this error?
[tt]"INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" & _
[tab]" VALUES ( " & Chr(34) & rstBase(PrimaryKeyField) & Chr(34) & "," & _
[tab]Chr(34) & fld.Name & Chr(34) & "," & _
[tab]Chr(34) & rstBase(fld.Name) & Chr(34) & " );"[/tt]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
Hi Ph,

I changed it where I hoped you ment me to and I got an invalid use of null error message whether I included the Nz code or not.

Do Until rstBase.EOF
If rstVarying.EOF = True Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" deleted ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields


db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & Replace(rstBase(fld.Name), "'", "''") & "');")
FieldChanged = True
Next fld

rstBase.MoveNext
ElseIf rstBase(PrimaryKeyField) > rstVarying(PrimaryKeyField) Then
ErrorMessage = "**** record " & rstVarying(PrimaryKeyField) & _
" added ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, NewText)" _
& " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & Replace(rstVarying(fld.Name), "'", "''") & "');")
FieldChanged = True
Next fld

rstVarying.MoveNext
ElseIf rstBase(PrimaryKeyField) < rstVarying(PrimaryKeyField) Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" deleted ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & Replace(rstBase(fld.Name), "'", "''") & "');")
FieldChanged = True
Next fld

rstBase.MoveNext
Else
FieldChanged = False
For Each fld In tdf.Fields
If Nz(rstBase(fld.Name)) <> Nz(rstVarying(fld.Name)) Then
If Not FieldChanged Then
ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
" Modified ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")
End If
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, OldText, NewText)" _
& " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & Replace(rstBase(fld.Name), "'", "''") & "','" & Replace(rstVarying(fld.Name), "'", "''") & "');")
FieldChanged = True
End If
Next fld
' If Not FieldChanged Then
' ErrorMessage = "Record " & rstBase(PrimaryKeyField) & _
' " identical"
' End If
rstBase.MoveNext
rstVarying.MoveNext
FieldChanged = False
End If
Loop
Do Until rstVarying.EOF
ErrorMessage = "**** record " & rstVarying(PrimaryKeyField) & _
" added to ****"
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber) " _
& "VALUES ( '" & ErrorMessage & "');")

For Each fld In tdf.Fields
db.Execute ("INSERT INTO TableDiscrepancies2 (RecordNumber, FieldName, NewText)" _
& " VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" & Replace(rstVarying(fld.Name), "'", "''") & "');")
FieldChanged = True
Next fld

rstVarying.MoveNext
Loop

Exit_CompareTables:
Set rstBase = Nothing
Set rstVarying = Nothing
db.Close
Exit Sub

Caution,

I also tried yours but still got the original error.


thanks as always for your help.
 
Upvote 0 Downvote
To avoid the 'Invalid use of Null' error:
Code: 
Replace([!]Nz([/!]rstVarying(fld.Name)[!], "<Null>")[/!], "'", "''")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks Ph,
It ran all the way through for the first time.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
349
Dan Stoica
Dan Stoica
Moss100
  • Locked
  • Question
Code not working 1
Replies
5
Views
236
Darrylles
Darrylles
renigar
  • Locked
  • Question
Code to write file path to field always writes to 1st record 1
Replies
5
Views
453
dhookom
dhookom
Moss100
  • Locked
  • Question
Vba code I can call from a query to reformat text
Replies
8
Views
330
Moss100
Moss100
AJAY0618
  • Locked
  • Question
How to pass a variable in VBA code by opening a form and storing the variable
Replies
3
Views
217
AJAY0618
AJAY0618

Part and Inventory Search

Sponsor

Back
Top