Converting database from Access97 to Access 2000-2003 with VBScripts

[LMHelper]

I am in the process of switching an Access Database that was originally designed in Access 97 and they need it to switch over to Access 2000-2003 Database Version. We have run into errors when moving it over (converting). The error I am stuck on now is Error 3251 which then brings me into a code window that states the following is not working and needs to be debugged:
rst2.FindFirst "[usedfpnumber] = " & varFPNum 'dcr 87 5 lines
I checked the table in use where this information is coming from and it has the correct information in it. The tableinUse table from the 97 version has the same information and set up as the new database copy in 2000-2003. Not sure why I'm getting this error and what to change in it.

Can someone assist me in some way as to what to look for?

[Denburt]

What type of recordset did you open? Show us the code you are using there.

Locates the first, last, next, or previous record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Jet workspaces only).


Example from the help file:
Set rstCustomers = dbsNorthwind.OpenRecordset( _
"SELECT CompanyName, City, Country " & _
"FROM Customers ORDER BY CompanyName", _
dbOpenSnapshot)

[LMHelper]

What type of recordset did you open? Show us the code you are using there.

Locates the first, last, next, or previous record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Jet workspaces only).


Example from the help file:
Set rstCustomers = dbsNorthwind.OpenRecordset( _
"SELECT CompanyName, City, Country " & _
"FROM Customers ORDER BY CompanyName", _
dbOpenSnapshot)

The error I am stuck on now is Error 3251 which then brings me into a code window that states the following is not working and needs to be debugged:
rst2.FindFirst "[usedfpnumber] = " & varFPNum 'dcr 87 5 lines

[Denburt]

The error I am stuck on now is Error 3251 which then brings me into a code window that states the following is not working and needs to be debugged:
rst2.FindFirst "[usedfpnumber] = " & varFPNum 'dcr 87 5 lines

I got this I need more code/info if I am going to help. There are several issue that can cause this.

I will take a stab though.
In your Declaration section do you see something like this:

Dim db as database
Dim rst2 as recordset

If you do try changing it to read:
Dim rst2 as Dao.recordset

I am pretty sure Dao is the default but it might depend on the order of your references.

Another thing that could cause this is declaring the recordset to something other than a snapshot or dynaset as per the help file comment that I posted

Example from the help file:
Set rstCustomers = dbsNorthwind.OpenRecordset( _
"SELECT CompanyName, City, Country " & _
"FROM Customers ORDER BY CompanyName", _
dbOpenSnapshot
 

These are two of the more common causes but I only know what you have told me so far. I will be more specific and ask you to post your declaration statement and the line that opens your recordset (should look similar to the one from the example above).

[ADezii]

I am in the process of switching an Access Database that was originally designed in Access 97 and they need it to switch over to Access 2000-2003 Database Version. We have run into errors when moving it over (converting). The error I am stuck on now is Error 3251 which then brings me into a code window that states the following is not working and needs to be debugged:
rst2.FindFirst "[usedfpnumber] = " & varFPNum 'dcr 87 5 lines
I checked the table in use where this information is coming from and it has the correct information in it. The tableinUse table from the 97 version has the same information and set up as the new database copy in 2000-2003. Not sure why I'm getting this error and what to change in it.

Can someone assist me in some way as to what to look for?

I don't think that it is anything more sinister than Syntax. A String Variable must be enclosed within single Quotes as in:

rst2.FindFirst "[LastName]='" & strName & "'"

[Denburt]

Still just poking in the dark but something to look for is a missing reference, upgrades can cause this. Probably the first thing you should do, in the VBA window go to tools then references and look for any that have missing next to it. If you went straight from 97 to 2003 this is a good possibility if I remember correctly these will be different versions. If this is the case uncheck it and scroll down till you see "Microsoft DAO 3.6 object Library" I like to move mine up to the third position from the top.

Well you have some ideas good luck let us know...

[nico5038]

1) Make sure that the reference (Open Tools/References when in VBA) to Microsoft Active Data Objects (default for your Access version) is unchecked
2) Make sure that Microsoft DAO version #.## is checked
3) Use for the opening of the recordset:

set rs2 = currentdb.openrecordset("<tablename or select statement>")

Nic;o)

[LMHelper]

What I am copying below is the entire SECTION that the problem is in. What is in bold is the error I am being brought to debug. Error 3251.


Sub ProcessFP(strSource As String, varFPNum As Variant, strFPNum As String) ' reww 072902 dcr 107
' reww created 10/26/01 to carry out functions formerly assigned to the GO button,
' which may or may not be on the form. strSource is from the label proc, and varFPNum is the numeric
' version of the FP number from the parse routine.
' This proc is new, but borrows and revises code from the 2 GO buttons, which this replaces.
' dcr 87 changes made beginning 7/8/02 reww

Dim strFPAction As String ' This specifies C, create, or U, update
Dim strFPType As String ' This is PTR or CR as appropriate
Dim strUpdErr1, strUpdErr2, strCreateErr As String
Dim db As Database
Dim rst, rst2 As Recordset ' dcr 87, added second recordset for tblInUse
Dim sql, strBldName As String
Dim intResponse As Integer ' msgbox function response value
Dim wksp As Workspace 'dcr 87 change
Set wksp = DBEngine(0)


strFPAction = Left(strSource, 1)
strFPType = Right(strSource, Len(strSource) - 1)

strUpdErr1 = "You have requested to update a " & strFPType & " Fixpackage which does not exist" _
& " in the requested build." & vbCrLf & "Please either create this " & strFPType & " Fixpackage or put in the " _
& "number of an existing " & strFPType & " Fixpackage."

strUpdErr2 = "You have requested to update a " & strFPType & " FixPackage, but the Release" _
& vbCrLf & "Name is incorrect for this " & strFPType & " FixPackage." & vbCrLf & _
"Click YES to update the selected " & strFPType & " FP and correct the Release Name." & vbCrLf _
& "Click NO to leave the Release Name as is and correct the " & strFPType & " Number." & vbCrLf _
& "Click CANCEL to erase both and start over."

strCreateErr = "You have requested to create a " & strFPType & " Fixpackage which already exists." _
& vbCrLf & "Please either update this " & strFPType & " Fixpackage or put in the " _
& "number of a new " & strFPType & " Fixpackage."

Set db = CurrentDb

'find the FP matching the number entered, checking only non-failed items
sql = "SELECT tblHeader.* FROM tblHeader WHERE (([strBuildName]= " _
& "'" & Me.txtBuildName & "') AND ([strStatus] <> 'Fail') AND ([strstatus] <> 'Fail-R')" _
& " and ([strPTRorCR]= " & "'" & strFPType & "'))"

Set rst = db.OpenRecordset(sql)
Set rst2 = db.OpenRecordset("tblInUse") 'dcr87 define the recordset for tblInUse

rst.FindFirst "[intFPNo] = " & varFPNum
'msgbox strFPType & " " & varFPNum & " " & strFPAction

rst2.FindFirst "[usedfpnumber] = " & varFPNum 'dcr 87 5 lines
If Not rst2.NoMatch Then
MsgBox "The FP requested, " & rst2!UsedFPN & " is in use."
Exit Sub
End If

' The following code implements a decision tree, using strFPAction to determine update or create.
' If no match, and strFPAction is U, then display error msg
' If no match, and strFPAction is C, then create the fixpackage
' If a match, and strFPAction is U, then update the fixpackage, checking release name, too
' If a match, and strFPAction is C, then display error msg
' In the following Msgboxes, vbCrLf is a carriage return and line feed
If rst.NoMatch Then
If strFPAction = "U" Then 'error trying to update an unknown cr or ptr
MsgBox strUpdErr1
Else ' go to PTRMain form to create a new FP, since strFPAction = "C"
DoCmd.OpenForm "frmPTRMain", , , , , , strFPAction & varFPNum & strFPType & Me.txtBuildName 'jb 9/4 Change# 100.
rst2.AddNew ' dcr 87 6 lines
rst2!UsedFPNumber = str(varFPNum)
rst2!UserName = wksp.UserName
rst2!UsedBuild = rst!strBuildName
rst2!UsedFPN = strFPNum 'reww 072902 dcr 107
rst2.Update
'Me.txtBuildName = "None Selected"
DoCmd.Close acForm, "frmWelcome", acSaveNo
End If
Else ' an FP match is found
'Now to first double check the build NAME (not ID!)
'Check the build name from the recordset against the build name from the welcome form
If strFPAction = "U" Then 'go to ptrmain form for update
strBldName = rst!strBuildName
If strBldName <> Me.txtBuildName Then 'Check for bad match of build names
intResponse = MsgBox(strUpdErr2, vbYesNoCancel + vbInformation, "Fixpackage")
Select Case intResponse
Case vbYes ' Keep FP, use Correct Build Name
Me.txtBuildName = strBldName
Case vbNo ' fix FP number
'Me.cboSelectCRList.Visible = True
'Me.lblCRList.Visible = True
'Me.txtGetCRNum.SetFocus
'Exit Sub
Case vbCancel ' start over
Me.txtBuildName = "None Selected"
Me.txtBuildName.SetFocus
Exit Sub
End Select 'intresponse
End If 'Check for match of build name
rst2.AddNew 'dcr 87 6 lines
rst2!UsedFPNumber = str(varFPNum)
rst2!UsedBuild = rst!strBuildName
rst2!UsedFPN = rst!strComplete
rst2!UserName = wksp.UserName
rst2.Update

DoCmd.OpenForm "frmPTRMain", , , , , , strFPAction & varFPNum & strFPType & Me.txtBuildName 'jb 904 Change# 100
'Me.txtBuildName = "None Selected"
DoCmd.Close acForm, "frmWelcome"
Else 'error trying to create an FP which exists
MsgBox strCreateErr
lblCreateCRFP_Click
End If 'strFPAction = "U"
End If 'rst.NoMatch



End Sub
'jb 4/24/02 added entire subroutine
Private Sub lblKeysite_Click()
'DoCmd.OpenForm "frmKeysiteList" 'jb 11/16
DoCmd.OpenForm "frmKeysiteSelect"
DoCmd.Close acForm, "frmWelcome", acSaveNo
End Sub

[Denburt]

O.K. code is good but before we can continue it is pertinent that you reply to the following. We have to start at the top and work our way down.

in the VBA window go to tools then references and look for any that have missing next to it. If you went straight from 97 to 2003 this is a good possibility if I remember correctly these will be different versions. If this is the case uncheck it and scroll down till you see "Microsoft DAO 3.6 object Library"

You should see "Microsoft DAO x.x object Library" If not then.... Let us know about this info please, thanks.

[LMHelper]

Ok so I went into Tools/References and made sure that my 3.6 was checked and it is. Seems all ok so I don't understand why I keep getting this error.

[Denburt]

O.K. after a bit of thought you might consider changing the following: "varFPNum As Variant" to a definitive numeric declaration such as a long, this will ensure the data type you recieve is numeric in value.

That said now consider using the following I have witnessed a few DB's that contain both DAO and ADO so it is good to specify that this needs to be a DOA recordset.

dim db as DAO.Database
Dim rst2 as DAO.Recordset
 

Once this is done let us know where you stand. Thanks

[Denburt]

O.K. I just reviewed your code again... And it looks like ADezii hit the nail on the head early in this one, although I didn't think it would raise the error you mentioned it looks like it did/does!

Two lines in question:

rst2.FindFirst "[usedfpnumber] = " & varFPNum 'dcr 87 5 lines
 
rst2!UsedFPNumber = str(varFPNum)

Since you are saving data in "UsedFPNumber" (obviously) as a string str(varFPNum) then you should be using the quotes as he suggested.

rst2.FindFirst "[usedfpnumber] = '" & varFPNum  & "'"

[LMHelper]

I never did close this out - our database conversion is completed. Thank you for all of your help.