dbSeeChanges are already there

G

gjtired

I run the following code and I get this error: You must use the
dbSeeChanges option with OpenRecordset when accessing a SQL Server
table that has an IDENTITY column. (Error 3622) I’m putting in the
dbSeeChanges but I’m still getting the error. Please help

Dim sqlString As String
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Dim varSQL As String
Dim varRegID As Integer


Set db = CurrentDb
varRegID = [Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub].Form!
[RegID]


sqlString = "SELECT tblCSP_Participant.CSPPart_ID,
tblCSP_Participant.RegID, tblCSP_Participant.BT_Youth,
tblCSP_Participant.P_F_Name , tblCSP_Participant.P_L_Name,
tblCSP_Participant.P_DOB," & _
"tblCSP_Participant.P_Gender, tblCSP_Participant.P_Relation_Child,
tblCSP_Participant.P_Language, tblCSP_Participant.P_Marital_Status,
tblCSP_Participant.P_Relation_Other_Adult,
tblCSP_Participant.S_F_Name, " & _
"tblCSP_Participant.S_L_Name, tblCSP_Participant.S_DOB,
tblCSP_Participant.S_Gender, tblCSP_Participant.S_Relation_Child,
tblCSP_Participant.S_Language, tblCSP_Participant.P_Y_F_Name, " & _
"tblCSP_Participant.P_Y_L_Name, tblCSP_Participant.P_Y_DOB,
tblCSP_Participant.P_Y_Gender, " & _
"tblCSP_Participant.Loc_Not_Home, tblCSP_Participant.Educ_Level,
tblCSP_Participant.School_Prog, " & _
"tblCSP_Participant.BTY_Program, tblCSP_Participant.FT_L_Name,
tblCSP_Participant.Community, " & _
"tblCSP_Participant.OY_F_Name1, tblCSP_Participant.OY_L_Name1,
tblCSP_Participant.OY_Age1, " & _
"tblCSP_Participant.OY_Gender1, tblCSP_Participant.OY_Home1,
tblCSP_Participant.OY_F_Name2, " & _
"tblCSP_Participant.OY_L_Name2, tblCSP_Participant.OY_Age2,
tblCSP_Participant.OY_Gender2, " & _
"tblCSP_Participant.OY_Home2, tblCSP_Participant.OY_F_Name3,
tblCSP_Participant.OY_L_Name3, " & _
"tblCSP_Participant.OY_Age3, tblCSP_Participant.OY_Gender3,
tblCSP_Participant.OY_Home3, " & _
"tblCSP_Participant.OY_F_Name4, tblCSP_Participant.OY_L_Name4,
tblCSP_Participant.OY_Age4, " & _
"tblCSP_Participant.OY_Gender4, tblCSP_Participant.OY_Home4,
tblCSP_Participant.OY_F_Name5, " & _
"tblCSP_Participant.OY_L_Name5, tblCSP_Participant.OY_Age5,
tblCSP_Participant.OY_Gender5, " & _
"tblCSP_Participant.OY_Home5, tblCSP_Participant.OY_F_Name6,
tblCSP_Participant.OY_L_Name6, " & _
"tblCSP_Participant.OY_Age6, tblCSP_Participant.OY_Gender6,
tblCSP_Participant.OY_Home6, " & _
"tblCSP_Participant.Case_F_Name, tblCSP_Participant.Case_L_Name,
tblCSP_Participant.Phone, " & _
"tblCSP_Participant.Edit_Date " & _
"from tblCSP_Participant " & _
"WHERE tblCSP_Participant.RegID=varRegID AND " & _
"tblCSP_Participant.Edit_Date =" & _
"( select Max(Edit_Date) " & _
" from tblCSP_Participant AS b " & _
"where b.RegID = tblCSP_Participant.RegID);"

Set rs = db.OpenRecordset(sqlString, , dbSeeChanges)
 
S

Sylvain Lafontaine

It's because you have forgot to write a value for the second parameter. In
VBA, only the last parameters in sequence (beginning from the end) are
optional; not median parameters.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I run the following code and I get this error: You must use the
dbSeeChanges option with OpenRecordset when accessing a SQL Server
table that has an IDENTITY column. (Error 3622) I’m putting in the
dbSeeChanges but I’m still getting the error. Please help

Dim sqlString As String
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Dim varSQL As String
Dim varRegID As Integer


Set db = CurrentDb
varRegID = [Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub].Form!
[RegID]


sqlString = "SELECT tblCSP_Participant.CSPPart_ID,
tblCSP_Participant.RegID, tblCSP_Participant.BT_Youth,
tblCSP_Participant.P_F_Name , tblCSP_Participant.P_L_Name,
tblCSP_Participant.P_DOB," & _
"tblCSP_Participant.P_Gender, tblCSP_Participant.P_Relation_Child,
tblCSP_Participant.P_Language, tblCSP_Participant.P_Marital_Status,
tblCSP_Participant.P_Relation_Other_Adult,
tblCSP_Participant.S_F_Name, " & _
"tblCSP_Participant.S_L_Name, tblCSP_Participant.S_DOB,
tblCSP_Participant.S_Gender, tblCSP_Participant.S_Relation_Child,
tblCSP_Participant.S_Language, tblCSP_Participant.P_Y_F_Name, " & _
"tblCSP_Participant.P_Y_L_Name, tblCSP_Participant.P_Y_DOB,
tblCSP_Participant.P_Y_Gender, " & _
"tblCSP_Participant.Loc_Not_Home, tblCSP_Participant.Educ_Level,
tblCSP_Participant.School_Prog, " & _
"tblCSP_Participant.BTY_Program, tblCSP_Participant.FT_L_Name,
tblCSP_Participant.Community, " & _
"tblCSP_Participant.OY_F_Name1, tblCSP_Participant.OY_L_Name1,
tblCSP_Participant.OY_Age1, " & _
"tblCSP_Participant.OY_Gender1, tblCSP_Participant.OY_Home1,
tblCSP_Participant.OY_F_Name2, " & _
"tblCSP_Participant.OY_L_Name2, tblCSP_Participant.OY_Age2,
tblCSP_Participant.OY_Gender2, " & _
"tblCSP_Participant.OY_Home2, tblCSP_Participant.OY_F_Name3,
tblCSP_Participant.OY_L_Name3, " & _
"tblCSP_Participant.OY_Age3, tblCSP_Participant.OY_Gender3,
tblCSP_Participant.OY_Home3, " & _
"tblCSP_Participant.OY_F_Name4, tblCSP_Participant.OY_L_Name4,
tblCSP_Participant.OY_Age4, " & _
"tblCSP_Participant.OY_Gender4, tblCSP_Participant.OY_Home4,
tblCSP_Participant.OY_F_Name5, " & _
"tblCSP_Participant.OY_L_Name5, tblCSP_Participant.OY_Age5,
tblCSP_Participant.OY_Gender5, " & _
"tblCSP_Participant.OY_Home5, tblCSP_Participant.OY_F_Name6,
tblCSP_Participant.OY_L_Name6, " & _
"tblCSP_Participant.OY_Age6, tblCSP_Participant.OY_Gender6,
tblCSP_Participant.OY_Home6, " & _
"tblCSP_Participant.Case_F_Name, tblCSP_Participant.Case_L_Name,
tblCSP_Participant.Phone, " & _
"tblCSP_Participant.Edit_Date " & _
"from tblCSP_Participant " & _
"WHERE tblCSP_Participant.RegID=varRegID AND " & _
"tblCSP_Participant.Edit_Date =" & _
"( select Max(Edit_Date) " & _
" from tblCSP_Participant AS b " & _
"where b.RegID = tblCSP_Participant.RegID);"

Set rs = db.OpenRecordset(sqlString, , dbSeeChanges)
 
D

Douglas J. Steele

I believe that an alternative to providing the second parameter (although I
don't know why you wouldn't) would be to used named parameters:

Set rs = db.OpenRecordset(name:=sqlString, options:=dbSeeChanges)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sylvain Lafontaine said:
It's because you have forgot to write a value for the second parameter.
In VBA, only the last parameters in sequence (beginning from the end) are
optional; not median parameters.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I run the following code and I get this error: You must use the
dbSeeChanges option with OpenRecordset when accessing a SQL Server
table that has an IDENTITY column. (Error 3622) I’m putting in the
dbSeeChanges but I’m still getting the error. Please help

Dim sqlString As String
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Dim varSQL As String
Dim varRegID As Integer


Set db = CurrentDb
varRegID = [Forms]![frmRegistrationSel]![sbfrmRegistrationSelSub].Form!
[RegID]


sqlString = "SELECT tblCSP_Participant.CSPPart_ID,
tblCSP_Participant.RegID, tblCSP_Participant.BT_Youth,
tblCSP_Participant.P_F_Name , tblCSP_Participant.P_L_Name,
tblCSP_Participant.P_DOB," & _
"tblCSP_Participant.P_Gender, tblCSP_Participant.P_Relation_Child,
tblCSP_Participant.P_Language, tblCSP_Participant.P_Marital_Status,
tblCSP_Participant.P_Relation_Other_Adult,
tblCSP_Participant.S_F_Name, " & _
"tblCSP_Participant.S_L_Name, tblCSP_Participant.S_DOB,
tblCSP_Participant.S_Gender, tblCSP_Participant.S_Relation_Child,
tblCSP_Participant.S_Language, tblCSP_Participant.P_Y_F_Name, " & _
"tblCSP_Participant.P_Y_L_Name, tblCSP_Participant.P_Y_DOB,
tblCSP_Participant.P_Y_Gender, " & _
"tblCSP_Participant.Loc_Not_Home, tblCSP_Participant.Educ_Level,
tblCSP_Participant.School_Prog, " & _
"tblCSP_Participant.BTY_Program, tblCSP_Participant.FT_L_Name,
tblCSP_Participant.Community, " & _
"tblCSP_Participant.OY_F_Name1, tblCSP_Participant.OY_L_Name1,
tblCSP_Participant.OY_Age1, " & _
"tblCSP_Participant.OY_Gender1, tblCSP_Participant.OY_Home1,
tblCSP_Participant.OY_F_Name2, " & _
"tblCSP_Participant.OY_L_Name2, tblCSP_Participant.OY_Age2,
tblCSP_Participant.OY_Gender2, " & _
"tblCSP_Participant.OY_Home2, tblCSP_Participant.OY_F_Name3,
tblCSP_Participant.OY_L_Name3, " & _
"tblCSP_Participant.OY_Age3, tblCSP_Participant.OY_Gender3,
tblCSP_Participant.OY_Home3, " & _
"tblCSP_Participant.OY_F_Name4, tblCSP_Participant.OY_L_Name4,
tblCSP_Participant.OY_Age4, " & _
"tblCSP_Participant.OY_Gender4, tblCSP_Participant.OY_Home4,
tblCSP_Participant.OY_F_Name5, " & _
"tblCSP_Participant.OY_L_Name5, tblCSP_Participant.OY_Age5,
tblCSP_Participant.OY_Gender5, " & _
"tblCSP_Participant.OY_Home5, tblCSP_Participant.OY_F_Name6,
tblCSP_Participant.OY_L_Name6, " & _
"tblCSP_Participant.OY_Age6, tblCSP_Participant.OY_Gender6,
tblCSP_Participant.OY_Home6, " & _
"tblCSP_Participant.Case_F_Name, tblCSP_Participant.Case_L_Name,
tblCSP_Participant.Phone, " & _
"tblCSP_Participant.Edit_Date " & _
"from tblCSP_Participant " & _
"WHERE tblCSP_Participant.RegID=varRegID AND " & _
"tblCSP_Participant.Edit_Date =" & _
"( select Max(Edit_Date) " & _
" from tblCSP_Participant AS b " & _
"where b.RegID = tblCSP_Participant.RegID);"

Set rs = db.OpenRecordset(sqlString, , dbSeeChanges)
Click to expand...
 
G

gjtired

I tried both suggestions.

Sylvain's

Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset,
dbSeeChanges)

I got the error: too few parameters. expected 1

Douglas's

Set rs = db.OpenRecordset(Name:=sqlString, Options:=dbSeeChanges)

I got the error: You must use the dbSeeChanges option with
OpenRecordset when accessing a SQL Server
table that has an IDENTITY column. (Error 3622)
 
S

Sylvain Lafontaine

Try adding dbOptimistic at the end:
Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges,
dbOptimistic)

Also, to avoid any confusion in the references between DAO and ADO, you
should always specify the right library when dimensioning your variables:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges,
dbOptimistic)

Also, it's possible that's your sqlString who is invalid; either because you
have a syntax error in it or because it's too complex for JET. When working
against ODBC linked tables, it doesn't take to much to put JET on its knees
when you start using sub-queries and Left and Right Outer Join and many
complexe queries that work perfectly well when executed against ordinary
tables will stop working when ODBC linked tables are involved.

Also, a final precision: it's a little false to say that you must absolutely
write the second parameter when you're writing the third one. It will be
more exact to say that not all combinaisons of parameters are valid but if
the default value provided for a missing parameter is part of an invalid
combination then Access won't try another one. The type of sqlString used
as well as the kind of source (here with one or more ODBC linked tables) is
of great importance on this. In case of doubt, provide the value of every
parameter that you can think of.
 
D

Douglas J. Steele

The reason for the too few parameters error is that you've included the
variable varRegID inside your SQL. Assuming RegID is a numeric field, it
should be:

"WHERE tblCSP_Participant.RegID=" & varRegID & " AND " & _

If it's text, it should be:

"WHERE tblCSP_Participant.RegID='" & varRegID & "' AND " & _

Exagerated for clarity, that second one is

"WHERE tblCSP_Participant.RegID= ' " & varRegID & " ' AND " & _

I guess I was wrong that you can use named parameters.
 
G

gjtired

Thank you so much it, wound up being a combination.

The end result was:

"WHERE tblCSP_Participant.RegID=" & varRegID & "

and

Set rs = db.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
 
Top