I am new to Visual Basic.  I am trying to access an Access database (Access 2000)
from within Visual Basic.  The big picture--The end user sees a map of the USA,
clicks on a particular state, and information either appears on a form, or an
Internet connection is triggered.

Here is the code that I am using with respect to database access:

'HANDLE BUTTONCLICK ON STATE
Private Sub Rose1_ButtonPress(ByVal ButtonName As String, ByVal ButtonIndex As
String)
'DEFINE THE ADO OBJECTS
          Dim adoConnect As ADODB.Connection
          Dim adoRecordset As ADODB.Recordset
          Dim adoCommand As ADODB.Command
          Dim s_SQL As String
'DYNAMICALLY BUILD THE CONNECTION
        Set adoConnect = New ADODB.Connection
        Set adoRecordset = New ADODB.Recordset

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
Info=True;Data Source=F:\PROJECT_NONPROFITS\Map2\stateinfo.mdb"
        adoConnect.Open ConnectionString
        If adoConnect.State = adStateOpen Then
          MsgBox "Open"
        Else
          MsgBox "Close"
        End If

        'THIS IS ONE WAY TO CREATE A RECORDSET AND IT WORKS
        'adoRecordset.Open "State_Hyperlinks", adoConnect
        'adoRecordset.MoveFirst
        'Do Until adoRecordset.EOF
        '            MsgBox adoRecordset("Url")
        '            adoRecordset.MoveNext
        'Loop
        s_SQL = "SELECT State_Hyperlinks.State From State_Hyperlinks WHERE
(((State_Hyperlinks.State)='WI'))"
        adoRecordset.Open s_SQL, adoConnect, adOpenDynamic, adLockOptimistic,
adCmdText
        If adoRecordset.State = adStateOpen Then
          MsgBox "Open"
        Else
          MsgBox "Close"
        End If
       MsgBox adoRecordset.RecordCount
       'MsgBox adoRecordset.("State_HyperLinks.State")

        adoRecordset.Close
        adoConnect.Close
        Set adoRecordset = Nothing
        Set adoConnection = Nothing

End Sub

As you will note, I have been testing whether the connection is open--it is.

I have also built a recordset with the commented out code--it works.

It is the SQL statement that runs into trouble.  I have tested it in Access and
it works--in fact the code is straight out of Access.  Originally, I was not
prefacing State with State_Hyperlinks, but I took the current format out of
Access.

I also check the recordset count.  It keeps returning -1, apparently meaning that
there are no records in the recordset.

When I write the SQL statement as SELECT * FROM State_Hyperlinks--it works.
I have read a good chunk of three Visual Basic Database programming books, but am
unable to see my way out of the box I am in.

Any assistance would be greatly appreciated.

Thanks

Jack Siegel

code the sql statement like this

"SELECT State From State_Hyperlinks WHERE
State='WI'"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I wish life were that easy.  That is the format I was using before I tried creating the SQL statement in Access and pasting it in.  Same result.

Thanks anyway

Jack Siegel

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jack,
A RecordCount of -1 is "by-design". Make sure to read the following ADO FAQ
http://www.able-consulting.com/ADO_Faq.htm#Q2

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

I am new to Visual Basic.  I am trying to access an Access database (Access
2000)
from within Visual Basic.  The big picture--The end user sees a map of the
USA,
clicks on a particular state, and information either appears on a form, or
an
Internet connection is triggered.

Here is the code that I am using with respect to database access:

'HANDLE BUTTONCLICK ON STATE
Private Sub Rose1_ButtonPress(ByVal ButtonName As String, ByVal ButtonIndex
As
String)
'DEFINE THE ADO OBJECTS
          Dim adoConnect As ADODB.Connection
          Dim adoRecordset As ADODB.Recordset
          Dim adoCommand As ADODB.Command
          Dim s_SQL As String
'DYNAMICALLY BUILD THE CONNECTION
        Set adoConnect = New ADODB.Connection
        Set adoRecordset = New ADODB.Recordset

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist
Security
Info=True;Data Source=F:\PROJECT_NONPROFITS\Map2\stateinfo.mdb"
        adoConnect.Open ConnectionString
        If adoConnect.State = adStateOpen Then
          MsgBox "Open"
        Else
          MsgBox "Close"
        End If

        'THIS IS ONE WAY TO CREATE A RECORDSET AND IT WORKS
        'adoRecordset.Open "State_Hyperlinks", adoConnect
        'adoRecordset.MoveFirst
        'Do Until adoRecordset.EOF
        '            MsgBox adoRecordset("Url")
        '            adoRecordset.MoveNext
        'Loop
        s_SQL = "SELECT State_Hyperlinks.State From State_Hyperlinks WHERE
(((State_Hyperlinks.State)='WI'))"
        adoRecordset.Open s_SQL, adoConnect, adOpenDynamic,
adLockOptimistic,
adCmdText
        If adoRecordset.State = adStateOpen Then
          MsgBox "Open"
        Else
          MsgBox "Close"
        End If
       MsgBox adoRecordset.RecordCount
       'MsgBox adoRecordset.("State_HyperLinks.State")

        adoRecordset.Close
        adoConnect.Close
        Set adoRecordset = Nothing
        Set adoConnection = Nothing

End Sub

As you will note, I have been testing whether the connection is open--it is.

I have also built a recordset with the commented out code--it works.

It is the SQL statement that runs into trouble.  I have tested it in Access
and
it works--in fact the code is straight out of Access.  Originally, I was not
prefacing State with State_Hyperlinks, but I took the current format out of
Access.

I also check the recordset count.  It keeps returning -1, apparently meaning
that
there are no records in the recordset.

When I write the SQL statement as SELECT * FROM State_Hyperlinks--it works.
I have read a good chunk of three Visual Basic Database programming books,
but am
unable to see my way out of the box I am in.

Any assistance would be greatly appreciated.

Thanks

Jack Siegel

Hello jack

Try after setting  the cursor Location to adUseClient and the cursor type to
adOpenKeyset

Sukesh

I am new to Visual Basic.  I am trying to access an Access database (Access
2000)
from within Visual Basic.  The big picture--The end user sees a map of the
USA,
clicks on a particular state, and information either appears on a form, or
an
Internet connection is triggered.

Here is the code that I am using with respect to database access:

'HANDLE BUTTONCLICK ON STATE
Private Sub Rose1_ButtonPress(ByVal ButtonName As String, ByVal ButtonIndex
As
String)
'DEFINE THE ADO OBJECTS
          Dim adoConnect As ADODB.Connection
          Dim adoRecordset As ADODB.Recordset
          Dim adoCommand As ADODB.Command
          Dim s_SQL As String
'DYNAMICALLY BUILD THE CONNECTION
        Set adoConnect = New ADODB.Connection
        Set adoRecordset = New ADODB.Recordset

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist
Security
Info=True;Data Source=F:\PROJECT_NONPROFITS\Map2\stateinfo.mdb"
        adoConnect.Open ConnectionString
        If adoConnect.State = adStateOpen Then
          MsgBox "Open"
        Else
          MsgBox "Close"
        End If

        'THIS IS ONE WAY TO CREATE A RECORDSET AND IT WORKS
        'adoRecordset.Open "State_Hyperlinks", adoConnect
        'adoRecordset.MoveFirst
        'Do Until adoRecordset.EOF
        '            MsgBox adoRecordset("Url")
        '            adoRecordset.MoveNext
        'Loop
        s_SQL = "SELECT State_Hyperlinks.State From State_Hyperlinks WHERE
(((State_Hyperlinks.State)='WI'))"
        adoRecordset.Open s_SQL, adoConnect, adOpenDynamic,
adLockOptimistic,
adCmdText
        If adoRecordset.State = adStateOpen Then
          MsgBox "Open"
        Else
          MsgBox "Close"
        End If
       MsgBox adoRecordset.RecordCount
       'MsgBox adoRecordset.("State_HyperLinks.State")

        adoRecordset.Close
        adoConnect.Close
        Set adoRecordset = Nothing
        Set adoConnection = Nothing

End Sub

As you will note, I have been testing whether the connection is open--it is.

I have also built a recordset with the commented out code--it works.

It is the SQL statement that runs into trouble.  I have tested it in Access
and
it works--in fact the code is straight out of Access.  Originally, I was not
prefacing State with State_Hyperlinks, but I took the current format out of
Access.

I also check the recordset count.  It keeps returning -1, apparently meaning
that
there are no records in the recordset.

When I write the SQL statement as SELECT * FROM State_Hyperlinks--it works.
I have read a good chunk of three Visual Basic Database programming books,
but am
unable to see my way out of the box I am in.

Any assistance would be greatly appreciated.

Thanks

Jack Siegel

The only cursor type available for adUseClient is adOpenStatic.

 - Tom

You are right

Thanks

Sukesh

The problem, after much work, has been resolved.  Thanks for assistance

Jack Siegel

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!