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.Op enRecordset( _
"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.Op enRecordset( _
"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.openr ecordset("<tabl ename 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(strSo urce 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.OpenRecordse t(sql)
Set rst2 = db.OpenRecordse t("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!UsedFPNumb er = str(varFPNum)
rst2!UserName = wksp.UserName
rst2!UsedBuild = rst!strBuildNam e
rst2!UsedFPN = strFPNum 'reww 072902 dcr 107
rst2.Update
'Me.txtBuildNam e = "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!strBuildNam e
If strBldName <> Me.txtBuildName Then 'Check for bad match of build names
intResponse = MsgBox(strUpdEr r2, vbYesNoCancel + vbInformation, "Fixpackage ")
Select Case intResponse
Case vbYes ' Keep FP, use Correct Build Name
Me.txtBuildName = strBldName
Case vbNo ' fix FP number
'Me.cboSelectCR List.Visible = True
'Me.lblCRList.V isible = 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!UsedFPNumb er = str(varFPNum)
rst2!UsedBuild = rst!strBuildNam e
rst2!UsedFPN = rst!strComplete
rst2!UserName = wksp.UserName
rst2.Update

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



End Sub
'jb 4/24/02 added entire subroutine
Private Sub lblKeysite_Clic k()
'DoCmd.OpenForm "frmKeysiteList " 'jb 11/16
DoCmd.OpenForm "frmKeysiteSele ct"
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.