VB6 ADO Insufficient Base Table information for updating or refreshing error (Why?) 
Author Message
 VB6 ADO Insufficient Base Table information for updating or refreshing error (Why?)

I can't seem to get this to work I am copying the connect and open code from
the code generated by the wizard. It seems fairly simple, I dont think there
is anything like a .edit in ADO and the recordset contains the primary key
field any ideas?

Private Sub cmdGenTestData_Click()
  Dim db As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim strQry As String
  Dim strSchoolJobID As String
  Dim strPhotoJobID As String
  Dim strStudentID As String

  Set rs = New ADODB.Recordset
  Set db = New ADODB.Connection
  strQry = "SELECT tblOrganizations.OrgJobNum,
tblPhotographers.PhotographerJobID FROM tblOrganizations, tblPhotographers
WHERE tblOrganizations.OrgID=1 AND tblPhotographers.PhotographerID=3;"

  db.CursorLocation = adUseClient
  db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
Source=C:\Output\test.mdb;"

  rs.Open strQry, db, adOpenDynamic, adLockOptimistic
  strSchoolJobID = rs!OrgJobNum
  strPhotoJobID = rs!PhotographerJobID

  rs.Close

  strQry = "SELECT tblSubjects.SubjectOrgIDNumber,
tblSubjects.SubjectPictureFilename, tblSubjects.SubjectJobIDNumber FROM
tblSubjects;"
  rs.Open strQry, db

    While Not rs.EOF
      strStudentID = rs!SubjectOrgIDNumber '******* This is the Primary Key
(string)
      rs!SubjectPictureFilename = strStudentID & ".jpg"
      rs!SubjectJobIDNumber = strSchoolJobID & "-" & strPhotoJobID & "-" &
strStudentID
'***** Error occurs here when I try to update ********8
      rs.Update
      rs.MoveNext
    Wend
    rs.Close

  db.Close

  Set rs = Nothing
  Set db = Nothing

End Sub



Wed, 30 Jan 2002 03:00:00 GMT  
 VB6 ADO Insufficient Base Table information for updating or refreshing error (Why?)
Dan,

On the end of the SQL statements you have a ;
Delete these and the update should work...

ie/strQry = "SELECT tblOrganizations.OrgJobNum,
tblPhotographers.PhotographerJobID FROM tblOrganizations,
tblPhotographers WHERE tblOrganizations.OrgID=1 AND
tblPhotographers.PhotographerID=3"

Hope this helps ...

--
Stuart Cox.
==========
(Analyst/Programmer-Johnston Engineering Ltd)
-----------------------------------------------------------



Quote:
> I can't seem to get this to work I am copying the connect and open
code from
> the code generated by the wizard. It seems fairly simple, I dont
think there
> is anything like a .edit in ADO and the recordset contains the
primary key
> field any ideas?

> Private Sub cmdGenTestData_Click()
>   Dim db As ADODB.Connection
>   Dim rs As ADODB.Recordset
>   Dim strQry As String
>   Dim strSchoolJobID As String
>   Dim strPhotoJobID As String
>   Dim strStudentID As String

>   Set rs = New ADODB.Recordset
>   Set db = New ADODB.Connection
>   strQry = "SELECT tblOrganizations.OrgJobNum,
> tblPhotographers.PhotographerJobID FROM tblOrganizations,
tblPhotographers
> WHERE tblOrganizations.OrgID=1 AND tblPhotographers.PhotographerID=3;"

>   db.CursorLocation = adUseClient
>   db.Open "PROVIDER=MSDataShape;Data

PROVIDER=Microsoft.Jet.OLEDB.3.51;Data

- Show quoted text -

Quote:
> Source=C:\Output\test.mdb;"

>   rs.Open strQry, db, adOpenDynamic, adLockOptimistic
>   strSchoolJobID = rs!OrgJobNum
>   strPhotoJobID = rs!PhotographerJobID

>   rs.Close

>   strQry = "SELECT tblSubjects.SubjectOrgIDNumber,
> tblSubjects.SubjectPictureFilename, tblSubjects.SubjectJobIDNumber
FROM
> tblSubjects;"
>   rs.Open strQry, db

>     While Not rs.EOF
>       strStudentID = rs!SubjectOrgIDNumber '******* This is the
Primary Key
> (string)
>       rs!SubjectPictureFilename = strStudentID & ".jpg"
>       rs!SubjectJobIDNumber = strSchoolJobID & "-" & strPhotoJobID &
"-" &
> strStudentID
> '***** Error occurs here when I try to update ********8
>       rs.Update
>       rs.MoveNext
>     Wend
>     rs.Close

>   db.Close

>   Set rs = Nothing
>   Set db = Nothing

> End Sub

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.


Fri, 01 Feb 2002 03:00:00 GMT  
 VB6 ADO Insufficient Base Table information for updating or refreshing error (Why?)
Stuart
Thank you, I can't believe that was it. I spent hours trying different
cursors diferent locking and diferent sql syntax. Was this something you
discovered, or is it documented somewhere?

Dan


Quote:
> Dan,

> On the end of the SQL statements you have a ;
> Delete these and the update should work...

> ie/strQry = "SELECT tblOrganizations.OrgJobNum,
> tblPhotographers.PhotographerJobID FROM tblOrganizations,
> tblPhotographers WHERE tblOrganizations.OrgID=1 AND
> tblPhotographers.PhotographerID=3"

> Hope this helps ...

> --
> Stuart Cox.
> ==========
> (Analyst/Programmer-Johnston Engineering Ltd)
> -----------------------------------------------------------



> > I can't seem to get this to work I am copying the connect and open
> code from
> > the code generated by the wizard. It seems fairly simple, I dont
> think there
> > is anything like a .edit in ADO and the recordset contains the
> primary key
> > field any ideas?

> > Private Sub cmdGenTestData_Click()
> >   Dim db As ADODB.Connection
> >   Dim rs As ADODB.Recordset
> >   Dim strQry As String
> >   Dim strSchoolJobID As String
> >   Dim strPhotoJobID As String
> >   Dim strStudentID As String

> >   Set rs = New ADODB.Recordset
> >   Set db = New ADODB.Connection
> >   strQry = "SELECT tblOrganizations.OrgJobNum,
> > tblPhotographers.PhotographerJobID FROM tblOrganizations,
> tblPhotographers
> > WHERE tblOrganizations.OrgID=1 AND tblPhotographers.PhotographerID=3;"

> >   db.CursorLocation = adUseClient
> >   db.Open "PROVIDER=MSDataShape;Data
> PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
> > Source=C:\Output\test.mdb;"

> >   rs.Open strQry, db, adOpenDynamic, adLockOptimistic
> >   strSchoolJobID = rs!OrgJobNum
> >   strPhotoJobID = rs!PhotographerJobID

> >   rs.Close

> >   strQry = "SELECT tblSubjects.SubjectOrgIDNumber,
> > tblSubjects.SubjectPictureFilename, tblSubjects.SubjectJobIDNumber
> FROM
> > tblSubjects;"
> >   rs.Open strQry, db

> >     While Not rs.EOF
> >       strStudentID = rs!SubjectOrgIDNumber '******* This is the
> Primary Key
> > (string)
> >       rs!SubjectPictureFilename = strStudentID & ".jpg"
> >       rs!SubjectJobIDNumber = strSchoolJobID & "-" & strPhotoJobID &
> "-" &
> > strStudentID
> > '***** Error occurs here when I try to update ********8
> >       rs.Update
> >       rs.MoveNext
> >     Wend
> >     rs.Close

> >   db.Close

> >   Set rs = Nothing
> >   Set db = Nothing

> > End Sub

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.



Fri, 01 Feb 2002 03:00:00 GMT  
 VB6 ADO Insufficient Base Table information for updating or refreshing error (Why?)

Quote:
>Thank you, I can't believe that was it. I spent hours trying different
>cursors diferent locking and diferent sql syntax. Was this something you
>discovered, or is it documented somewhere?

I'm not sure if it's documented anywhere, but with most databases, if you use
the semicolon to terminate the SQL string (which you should do for good
measure), you need to enclose multiple-case WHERE clauses in parentheses.

"... WHERE (condition AND condition);"

-Curtis Spendlove
-Solstice Software



Fri, 01 Feb 2002 03:00:00 GMT  
 VB6 ADO Insufficient Base Table information for updating or refreshing error (Why?)
I think the problem is that you can't write to a query.

Quote:

>I can't seem to get this to work I am copying the connect and open code
from
>the code generated by the wizard. It seems fairly simple, I dont think
there
>is anything like a .edit in ADO and the recordset contains the primary key
>field any ideas?

>Private Sub cmdGenTestData_Click()
>  Dim db As ADODB.Connection
>  Dim rs As ADODB.Recordset
>  Dim strQry As String
>  Dim strSchoolJobID As String
>  Dim strPhotoJobID As String
>  Dim strStudentID As String

>  Set rs = New ADODB.Recordset
>  Set db = New ADODB.Connection
>  strQry = "SELECT tblOrganizations.OrgJobNum,
>tblPhotographers.PhotographerJobID FROM tblOrganizations, tblPhotographers
>WHERE tblOrganizations.OrgID=1 AND tblPhotographers.PhotographerID=3;"

>  db.CursorLocation = adUseClient
>  db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
>Source=C:\Output\test.mdb;"

>  rs.Open strQry, db, adOpenDynamic, adLockOptimistic
>  strSchoolJobID = rs!OrgJobNum
>  strPhotoJobID = rs!PhotographerJobID

>  rs.Close

>  strQry = "SELECT tblSubjects.SubjectOrgIDNumber,
>tblSubjects.SubjectPictureFilename, tblSubjects.SubjectJobIDNumber FROM
>tblSubjects;"
>  rs.Open strQry, db

>    While Not rs.EOF
>      strStudentID = rs!SubjectOrgIDNumber '******* This is the Primary Key
>(string)
>      rs!SubjectPictureFilename = strStudentID & ".jpg"
>      rs!SubjectJobIDNumber = strSchoolJobID & "-" & strPhotoJobID & "-" &
>strStudentID
>'***** Error occurs here when I try to update ********8
>      rs.Update
>      rs.MoveNext
>    Wend
>    rs.Close

>  db.Close

>  Set rs = Nothing
>  Set db = Nothing

>End Sub



Sat, 02 Feb 2002 03:00:00 GMT  
 VB6 ADO Insufficient Base Table information for updating or refreshing error (Why?)
Dan,

I havn't seen this documented but like you, I spent hours looking for
the problem only to discover this as the solution.

--
Stuart Cox.
==========
(Analyst/Programmer-Johnston Engineering Ltd)
----------------------------------------------------------------------


Quote:
> Stuart
> Thank you, I can't believe that was it. I spent hours trying different
> cursors diferent locking and diferent sql syntax. Was this something
you
> discovered, or is it documented somewhere?

> Dan



> > Dan,

> > On the end of the SQL statements you have a ;
> > Delete these and the update should work...

> > ie/strQry = "SELECT tblOrganizations.OrgJobNum,
> > tblPhotographers.PhotographerJobID FROM tblOrganizations,
> > tblPhotographers WHERE tblOrganizations.OrgID=1 AND
> > tblPhotographers.PhotographerID=3"

> > Hope this helps ...

> > --
> > Stuart Cox.
> > ==========
> > (Analyst/Programmer-Johnston Engineering Ltd)
> > -----------------------------------------------------------



> > > I can't seem to get this to work I am copying the connect and open
> > code from
> > > the code generated by the wizard. It seems fairly simple, I dont
> > think there
> > > is anything like a .edit in ADO and the recordset contains the
> > primary key
> > > field any ideas?

> > > Private Sub cmdGenTestData_Click()
> > >   Dim db As ADODB.Connection
> > >   Dim rs As ADODB.Recordset
> > >   Dim strQry As String
> > >   Dim strSchoolJobID As String
> > >   Dim strPhotoJobID As String
> > >   Dim strStudentID As String

> > >   Set rs = New ADODB.Recordset
> > >   Set db = New ADODB.Connection
> > >   strQry = "SELECT tblOrganizations.OrgJobNum,
> > > tblPhotographers.PhotographerJobID FROM tblOrganizations,
> > tblPhotographers
> > > WHERE tblOrganizations.OrgID=1 AND

tblPhotographers.PhotographerID=3;"

- Show quoted text -

Quote:

> > >   db.CursorLocation = adUseClient
> > >   db.Open "PROVIDER=MSDataShape;Data
> > PROVIDER=Microsoft.Jet.OLEDB.3.51;Data
> > > Source=C:\Output\test.mdb;"

> > >   rs.Open strQry, db, adOpenDynamic, adLockOptimistic
> > >   strSchoolJobID = rs!OrgJobNum
> > >   strPhotoJobID = rs!PhotographerJobID

> > >   rs.Close

> > >   strQry = "SELECT tblSubjects.SubjectOrgIDNumber,
> > > tblSubjects.SubjectPictureFilename, tblSubjects.SubjectJobIDNumber
> > FROM
> > > tblSubjects;"
> > >   rs.Open strQry, db

> > >     While Not rs.EOF
> > >       strStudentID = rs!SubjectOrgIDNumber '******* This is the
> > Primary Key
> > > (string)
> > >       rs!SubjectPictureFilename = strStudentID & ".jpg"
> > >       rs!SubjectJobIDNumber = strSchoolJobID & "-" &
strPhotoJobID &
> > "-" &
> > > strStudentID
> > > '***** Error occurs here when I try to update ********8
> > >       rs.Update
> > >       rs.MoveNext
> > >     Wend
> > >     rs.Close

> > >   db.Close

> > >   Set rs = Nothing
> > >   Set db = Nothing

> > > End Sub

> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.


Sat, 02 Feb 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Error: Insufficient base table information for updating or refreshing

2. Error 2147467259 Insufficient base table information for updating or refreshing

3. Insufficient base table information for updating or refreshing.

4. Help .....insufficient base table information for updating or refreshing

5. Insufficient base table information for updating or refreshing

6. Insufficient base table information for updating or refreshing

7. Insufficient base table information for updating or refreshing.

8. Insufficient base table information for updating or refreshing

9. XP installation : 2147467259 Insufficient base table information for updating or refreshing

10. Insufficient base table information for updating or refreshing

11. Insufficient base table information for updating or refreshing?

12. AdodcSave:-2147467259 - Insufficient base table information for updatig or refreshing

 

 
Powered by phpBB® Forum Software