Strange Error from Access

[JimS]

The following rather simple code:

Option Compare Database
Option Explicit
Sub ParseDrawings()
Dim FC As ADODB.Connection
Dim FCRST As New ADODB.Recordset
Dim DrawingTable() As String
Dim I As Integer
On Error Resume Next
Set FC = CurrentProject.Connection
FCRST.Open "Select * from [system list] where left([Drawing
boundaries],3) = 'PWC';", FC, adOpenForwardOnly, adLockReadOnly
FCRST.MoveFirst
Do While FCRST.EOF = False
DrawingTable = Split(FCRST.Fields("Drawing Boundaries"))
For I = 0 To UBound(DrawingTable)
DoCmd.RunSQL "Insert into [DrwSyst] ([DrawingID], [Syst]) values ('"
& _
DrawingTable(I) & "','" & FCRST.Fields("Sys #") & "');"
Next I
FCRST.MoveNext
Loop
End Sub

 

[Robert Morley]

Is this being executed against a SQL Server database or an Access database?

The ";" is optional in Access, even though the QBE always puts it in, but I
believe it's unrecognized in SQL Server unless you're using multiple
recordsets. Either way, it can be removed safely. So try removing the ";"
from the end of the query and also see if using a LIKE operator helps any
(no reason it should, but it's a bit more standard and if nothing else, your
query will probably execute faster). So your query would read:

SELECT * FROM [system list] WHERE [Drawing boundaries] LIKE 'PWC%'

If you're executing against an Access database, change the % at the end for
a *, unless you're in ANSI-92 compatible mode.



Rob

 

[JimS]

Rob, the select statement seems to work fine on the Access database, though I
will remember the "%" and the like operator. It's the "movenext" method that
blows up (unless I use the "on error resume next" statement as shown...)
--
Jim

Is this being executed against a SQL Server database or an Access database?

The ";" is optional in Access, even though the QBE always puts it in, but I
believe it's unrecognized in SQL Server unless you're using multiple
recordsets. Either way, it can be removed safely. So try removing the ";"
from the end of the query and also see if using a LIKE operator helps any
(no reason it should, but it's a bit more standard and if nothing else, your
query will probably execute faster). So your query would read:

SELECT * FROM [system list] WHERE [Drawing boundaries] LIKE 'PWC%'

If you're executing against an Access database, change the % at the end for
a *, unless you're in ANSI-92 compatible mode.



Rob

The following rather simple code:

Option Compare Database
Option Explicit
Sub ParseDrawings()
Dim FC As ADODB.Connection
Dim FCRST As New ADODB.Recordset
Dim DrawingTable() As String
Dim I As Integer
On Error Resume Next
Set FC = CurrentProject.Connection
FCRST.Open "Select * from [system list] where left([Drawing
boundaries],3) = 'PWC';", FC, adOpenForwardOnly, adLockReadOnly
FCRST.MoveFirst
Do While FCRST.EOF = False
DrawingTable = Split(FCRST.Fields("Drawing Boundaries"))
For I = 0 To UBound(DrawingTable)
DoCmd.RunSQL "Insert into [DrwSyst] ([DrawingID], [Syst]) values
('"
& _
DrawingTable(I) & "','" & FCRST.Fields("Sys #") & "');"
Next I
FCRST.MoveNext
Loop
End Sub
-----------------
....generates an "Unknown Error" -2147467259 (8000-4005) on execution of
the "FCRST.Movenext" statement when it moves past the last record in the
recordset. I can ignore the error, but I can't stop it from being
generated.
Why the error? Why there?

 

[Robert Morley]

If you haven't already, try removing the semi-colon from the select
statement, as that might be causing SQL Server to think there's another
recordset it should be moving to when it hits the end of the recordset
(which would be why it errors during the .MoveNext, not during the .Open).
I can't produce that effect on my end, but it's worth a shot, at least. You
can also try changing the DoCmd.RunSQL to CurrentProject.Connection.Execute.
All of this *should* make no difference at all, but worth a shot, anyway.

One final thing to check is that your SQL Server database isn't corrupt in
some fashion. I don't do it very often, so don't ask me for the specifics
of the commands, but there's DBCC CHECKDB and DBCC CHECKTABLE that you'll
probably want to run against your back end and see if that's the problem.

Other than that, I don't know what to suggest. I've never seen a crash
related to simply hitting the end of a recordset before.



Rob

 

[JimS]

Research suggests it's because it's actually an excel spreadsheet I'm
reading, using Access links. I'll try removing the semicolon.

 

[JimS]

Removing the ";" had no effect, good or bad....

 

[Robert Morley]

If that's the case, I can't help you, sorry. I've rarely ever linked on a
programmatic basis, usually just use the wizards to import data and go from
there.


Rob