ADO Newbie Question

[V(ery) Basic]

Ok. I've just started databases since this is the first time I need to use one

Can anybody tell me why this won't work:

Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim sql As String

conn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Visual Studio\vb98\Players.mdb")
conn.Open

sql = "Select * From First_Name"
'Raises an error: The Microsoft Jet Database engine cannot find the input
'table or query 'First_Name'. Make sure it exists and that its name is
'spelled correctly
Call rs.Open(sql, conn)

The path is correct. I'm pretty sure about the 'First_Name' bit. That's what it says on top of the column in Access' Datasheet view. I also tried it using 'FirstName' because that's what the box says in it in Access' Design View.

Also, I can't use any compenents, because when it works, I'm going to turn it into ASP (and I'll use CreateObject and all that)

I looked through my VB Book and the Tutorials on this site, but they all use some sort of control.

Any help would be appreciated,

Me.

[DrewDog_21]

First, take out the Call reserve word in Call rs.Open
You don't need it.

Second, it appears that in your database, you have a table with a column called First_Name. In your SQL statement, you want to use the name of the table and not the column, so it would be "Select * From " and then the table name, like this:

Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim sql As String

conn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Visual Studio\vb98\Players.mdb")
conn.Open

sql = "Select * From 'and then the table name
rs.Open(sql, conn)

[V(ery) Basic]

I'm afraid I'd already tried all of the possible name cominations in the SQL script:

'I've tried:
sql = "Select * From [First Name]"
'And:
sql = "Select * From First_Name"
'AND
sql = "Select * From FirstName"

But they all raise the error I explained.

Maybe there's something else?

Please help, I'll give you a really big thanks,

Moi.

[sanon]

Make sure that your program is really connected to the database. You can check it in the debug mode or insert a statement in your code follow this:

If conn.state = adstateclose then
   msgbox "Can't connect to the database."
endif

[V(ery) Basic]

I tried it with adStateClosed and it always goes through the 'else' bit I added, meaining that it is connected, right?

Maybe there are different 'providers' I could use instead of that Jet thing? (I'm using Access 97)

Any help very welcome indeed.

[Gary.Lowe]

V(ery) Basic

Is First_Name a field in the table because you need to specify the table name itself in the SQL

sSQL = SELECT * FROM tblCustomers

rs.Open sSQL conn

This code works perfect for me

I am using access 2k.
but it should work with '97 too.

Private Sub Form_Load()
    Dim oCon As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim sSQL As String

    Set oCon = New ADODB.Connection
    Set oRS = New ADODB.Recordset

    With oCon
        .ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=c:\my documents\MyPages.mdb;"
        .Open
    End With
    'tblMyPages is the table, PageID is the field.
    sSQL = "SELECT * FROM tblMyPages WHERE PageID='LTEd'"
    Set oRS = oCon.Execute(sSQL)
    'content is a field of my DB
    txtText = oRS("Content")
    oCon.Close
End Sub

[Edited by denniswrenn on 09-10-2000 at 12:15 PM]

[V(ery) Basic]

Ok.

Thanks y'all. it now works, you see the problem was that I thought that you should pass the name of the field, not the entire table.

Oops. I told you I was a Database beginner, didn't I?

It now works like a house on fire.

That's not right is it?