K-9
asked on
Help Needed!! MS Access/SQL problem
This is worth alot, since this needs to be answered quickly.
I have a MS Access DB, which I upgraded to the SQL Server. I can open the SQL server fine, but when I hit a certain point in my code it fails with the error "Run-time error '3251'" "Operation is not supported for this type of object"
Here is the code:
Dim rstcallserv As Recordset
If BttnStatus(FormNumber, LastBttn) = "ADD" Then
If Form_INCIDENT.Incno <> 0 And Form_INCIDENT.Incno <> "" Then
Set rstcallserv = DBBACKEND.OpenRecordset("C ALLSERV", dbOpenDynaset, dbSeeChanges)
With rstcallserv
rstcallserv.Index = "INCNO" <<Offending code
rstcallserv.Seek "=", Form_INCIDENT.Incno << Offending code
.Edit
Form_INCIDENT.details1 = !details1
Form_INCIDENT.OCCFrom = !DATECOMP
Form_INCIDENT.OCCTo = !DATECOMP
Form_INCIDENT.ReviewDate = !DATECOMP
Form_INCIDENT.TimeFrom = !TIMECOMP
Form_INCIDENT.TimeTo = !TIMECOMP
End With
rstcallserv.Close
End If
End If
Me!Incno.BackColor = 16777215
End Sub
--------
The error pops up at the .index statement, and if I comment that out, it appears on the .seek as well. I looked through MSDN, and they suggest it could be because the table is read-only. But its not. This code works perfectly in an all MS-Access setup. Is there a substitute to the .index & .seek command for sql?
What this code does, is after you put in an incident number, it then retrieves data in another table based off of that incident number.
Please advise..
I have a MS Access DB, which I upgraded to the SQL Server. I can open the SQL server fine, but when I hit a certain point in my code it fails with the error "Run-time error '3251'" "Operation is not supported for this type of object"
Here is the code:
Dim rstcallserv As Recordset
If BttnStatus(FormNumber, LastBttn) = "ADD" Then
If Form_INCIDENT.Incno <> 0 And Form_INCIDENT.Incno <> "" Then
Set rstcallserv = DBBACKEND.OpenRecordset("C
With rstcallserv
rstcallserv.Index = "INCNO" <<Offending code
rstcallserv.Seek "=", Form_INCIDENT.Incno << Offending code
.Edit
Form_INCIDENT.details1 = !details1
Form_INCIDENT.OCCFrom = !DATECOMP
Form_INCIDENT.OCCTo = !DATECOMP
Form_INCIDENT.ReviewDate = !DATECOMP
Form_INCIDENT.TimeFrom = !TIMECOMP
Form_INCIDENT.TimeTo = !TIMECOMP
End With
rstcallserv.Close
End If
End If
Me!Incno.BackColor = 16777215
End Sub
--------
The error pops up at the .index statement, and if I comment that out, it appears on the .seek as well. I looked through MSDN, and they suggest it could be because the table is read-only. But its not. This code works perfectly in an all MS-Access setup. Is there a substitute to the .index & .seek command for sql?
What this code does, is after you put in an incident number, it then retrieves data in another table based off of that incident number.
Please advise..
just a couple of questions first.
-when you use DBBACKEND, is this a connection or is this a DAO.Database object?
-is CALLSERV a storedprocedure or a table? i am guessing it's a table.
-a97, a2k, or axp?
-using ado or dao?
depending on your answers, i would change some of the ways you do things. personally, i would create a stored procedure that returns the result you are looking for. open a connection to the sql server and then open a recordset based of the connection and the stored procedure. you don't really need a stored procedure, it'd just be a bit faster though i don't know if you'd ever notice the difference...
but aside from all of that. try this code in place of what you had.
Set rstcallserv = DBBACKEND.OpenRecordset("S ELECT * " _
& "FROM CALLSERV " _
& "WHERE Inco = " & Form_INCIDENT.Incno, _
dbOpenDynaset, _
dbSeeChanges)
With rstcallserv
'you shouldn't need this .Edit
Form_INCIDENT.details1 = !details1
Form_INCIDENT.OCCFrom = !DATECOMP
Form_INCIDENT.OCCTo = !DATECOMP
Form_INCIDENT.ReviewDate = !DATECOMP
Form_INCIDENT.TimeFrom = !TIMECOMP
Form_INCIDENT.TimeTo = !TIMECOMP
End With
let me know if this doesn't help or you need more help...
dovholuk
-when you use DBBACKEND, is this a connection or is this a DAO.Database object?
-is CALLSERV a storedprocedure or a table? i am guessing it's a table.
-a97, a2k, or axp?
-using ado or dao?
depending on your answers, i would change some of the ways you do things. personally, i would create a stored procedure that returns the result you are looking for. open a connection to the sql server and then open a recordset based of the connection and the stored procedure. you don't really need a stored procedure, it'd just be a bit faster though i don't know if you'd ever notice the difference...
but aside from all of that. try this code in place of what you had.
Set rstcallserv = DBBACKEND.OpenRecordset("S
& "FROM CALLSERV " _
& "WHERE Inco = " & Form_INCIDENT.Incno, _
dbOpenDynaset, _
dbSeeChanges)
With rstcallserv
'you shouldn't need this .Edit
Form_INCIDENT.details1 = !details1
Form_INCIDENT.OCCFrom = !DATECOMP
Form_INCIDENT.OCCTo = !DATECOMP
Form_INCIDENT.ReviewDate = !DATECOMP
Form_INCIDENT.TimeFrom = !TIMECOMP
Form_INCIDENT.TimeTo = !TIMECOMP
End With
let me know if this doesn't help or you need more help...
dovholuk
I would remove the rstcallserv lines after the with statement.
With rstcallserv
.Index = "INCNO"
.Seek "=", Form_INCIDENT.Incno
With a "With" statement it is not necessary to specify the object repeatedly.
With rstcallserv
.Index = "INCNO"
.Seek "=", Form_INCIDENT.Incno
With a "With" statement it is not necessary to specify the object repeatedly.
ASKER
Thanks for the help so far.. Dovholuk, when I try your suggestion it gives me an error "Run-time error 3464" "Data type mismatch in criteria expression" Both incno fields in the callserv & this table are set to "Text" I am running Access2000 as a front end. And Callserv is a table.
I also got a "with" error when I had the with rstcallserv statement in there. I took it out & used
Form_INCIDENT.details1 = rstcallserv!details1
etc...
Also, I know this may sound like a dumb question to u guys, but how can I create a stored procedure, and how do I call it from access?
Thanks for your help so far!
I also got a "with" error when I had the with rstcallserv statement in there. I took it out & used
Form_INCIDENT.details1 = rstcallserv!details1
etc...
Also, I know this may sound like a dumb question to u guys, but how can I create a stored procedure, and how do I call it from access?
Thanks for your help so far!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What version of Access, and if 2K are you using an ADP?
The original error message you're getting seems to indicate you have an ADO recordset rather than a DAO recordset. ADO recordsets have no Seek method.
When you define your RS you can specify a DAO.Recordset or ADODB.Recordset (assuming the proper references are in your db)
The original error message you're getting seems to indicate you have an ADO recordset rather than a DAO recordset. ADO recordsets have no Seek method.
When you define your RS you can specify a DAO.Recordset or ADODB.Recordset (assuming the proper references are in your db)
ASKER
TY very much for the help.. It works good :)
I have another simple question, Ill post it here, but I can give you points for it as well. I created a simple find button on the access form. Well, it seems toe work when I type in a name that I know exists, however if I type one that doesn't exists, or do a to only match "part of the field" it will just hang. I can press ctrl+break to stop the search, but pushing ctrl+alt+delete, shows it "not responding" in the task manager. Is there another way around this?
Thanks again!
I have another simple question, Ill post it here, but I can give you points for it as well. I created a simple find button on the access form. Well, it seems toe work when I type in a name that I know exists, however if I type one that doesn't exists, or do a to only match "part of the field" it will just hang. I can press ctrl+break to stop the search, but pushing ctrl+alt+delete, shows it "not responding" in the task manager. Is there another way around this?
Thanks again!
what i do to match "part of a field" is i create an if statement that determines if the user is looking for an exact match or any part of the field. then in the appropriate if else statement i put something like:
'(ASSUME THE FOLLOWING:)
'You have 1 form named MyForm
'You have 1 control named txtMyTextBox (or whatever)
'You have 1 checkbox named chkMatchExact labeled "Match Exactly" or something similiar
'an open connection to DBBACKEND somewhere on an SQL server 'since you're using SQL server
' i thought i'd build this example around SQL server
'a table named CALLSERV with a field named INCNO
if me!chkMatchExact = true then
set rs = DBBACKEND.OpenRecordset("S ELECT * FROM CALLSERV WHERE INCNO = '" & me!txtMyTextBox & "'")
else
'the user wants to match any part of the field
set rs = DBBACKEND.OpenRecordset("S ELECT * FROM CALLSERV WHERE INCNO like '%" & me!txtMyTextBox & "%'")
end if
if not rs.eof then
'the recordset is NOT empty so do something...
end if
help any? need more help?
dovholuk
'(ASSUME THE FOLLOWING:)
'You have 1 form named MyForm
'You have 1 control named txtMyTextBox (or whatever)
'You have 1 checkbox named chkMatchExact labeled "Match Exactly" or something similiar
'an open connection to DBBACKEND somewhere on an SQL server 'since you're using SQL server
' i thought i'd build this example around SQL server
'a table named CALLSERV with a field named INCNO
if me!chkMatchExact = true then
set rs = DBBACKEND.OpenRecordset("S
else
'the user wants to match any part of the field
set rs = DBBACKEND.OpenRecordset("S
end if
if not rs.eof then
'the recordset is NOT empty so do something...
end if
help any? need more help?
dovholuk
ASKER
Well it does help, but I have like 20 different text boxes on that form, and have about 50 other forms as well in this DB. Its quite large. I hope there's an easier way to doing this.
I ran another test, and it seems to sometimes find a match, sometimes just hang, even if the data exists in that field. Do I need to set something up at the sql server to allow the searches to run at 100% ?
I ran another test, and it seems to sometimes find a match, sometimes just hang, even if the data exists in that field. Do I need to set something up at the sql server to allow the searches to run at 100% ?
In this case the SP would just take the Incno as a parameter and just return the info that needs displaying - so locking records for less time and will use the sql server query optimiser to cache the query plan.