Accessing a table via VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to access a table using a module in Access 2003. The help
documentation tells me to add this line:

Dim dbs As Database

I get an error that says "User-defined type not defined"

Dim cnn As New ADODB.Connection
works in another sub routine.

What am I missing?

Tim
 
TimD said:
I am trying to access a table using a module in Access 2003. The help
documentation tells me to add this line:

Dim dbs As Database

I get an error that says "User-defined type not defined"

Dim cnn As New ADODB.Connection
works in another sub routine.

What am I missing?

Tim
Click to expand...

Database is a DAO object. If you are using Access 2000 or 2002 then there is no
reference to DAO by default. ADO has no database object.

You need to add a reference to DAO or find an ADO equivelant of what you want to
do.
 
Dim rs as DAO.Recordset
Set rs = CurrentDB.OpenRecordset([SQLStatement/Table Name], [parameters])

rs.close
Set rs = Nothing

You should have a reference already set to the DAO object library. If
not, you'll need it. FYI - You may have both the DAO and ADO object
library set or neither. If you have both you have to qualify the
statement as I've done (or ADO.Database). I'm thinking though that you
don't have either (which would be odd).
 
OK, that worked. Now...

The examples I see all seem to ask you to have the database name in the
connection string.

Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb", True)

Is there a way to use something like CurrentConnection?

I am trying to create a "User Environment" table that will be accessed at
start up of the database in Access. Some of the variables will be
validated,i.e. directory paths.

Tim
 
I'm a bit more versed in DAO (hint). But have some dabbling in ADO. As I
seem to recall, it is possible to reuse a open connection.
 
Connections are stricktly an ADO thing. I would venture to say that the
heart of DAO (when it comes to record-level processing) is the database
& recordSet objects, which if need be can be resused as in...

Dim db as DAO.Database
Dim rs as DAO.RecordSet

Set db = CurrentDB
Set rs = db.OpenRecordset("tblTransports", dbOpenForwardOnly)

While not rs.eof
[do something]
wend

rs.close

Set rs = db.OpenRecordset("tblLocations", dbOpenForwardOny)

While not rs.eof
[do something]
wend

rs.close
Set rs = Nothing
Set db = Nothing

In the example able, the recordset object is reused, as opposed to
destroying it and then recreating it.
 
Thank you, I got it now.

David C. Holley said:
Connections are stricktly an ADO thing. I would venture to say that the
heart of DAO (when it comes to record-level processing) is the database
& recordSet objects, which if need be can be resused as in...

Dim db as DAO.Database
Dim rs as DAO.RecordSet

Set db = CurrentDB
Set rs = db.OpenRecordset("tblTransports", dbOpenForwardOnly)

While not rs.eof
[do something]
wend

rs.close

Set rs = db.OpenRecordset("tblLocations", dbOpenForwardOny)

While not rs.eof
[do something]
wend

rs.close
Set rs = Nothing
Set db = Nothing

In the example able, the recordset object is reused, as opposed to
destroying it and then recreating it.
I would prefer the DAO version. I could use code samples.

:
Click to expand...
Click to expand...
 
You can also cheat somewhat by using

Set rs = CurrentDb.OpenrecordSet

and forgo creating a reference to the database, however if you're
reusing the rs object, you probably want to go with a specifically
instantiated db object. If I understanding things correctly CurrentDb()
returns a reference to the current DB, but its a different reference
everytime - hence there's probably more overhead invovled.
Thank you, I got it now.

:

Connections are stricktly an ADO thing. I would venture to say that the
heart of DAO (when it comes to record-level processing) is the database
& recordSet objects, which if need be can be resused as in...

Dim db as DAO.Database
Dim rs as DAO.RecordSet

Set db = CurrentDB
Set rs = db.OpenRecordset("tblTransports", dbOpenForwardOnly)

While not rs.eof
[do something]
wend

rs.close

Set rs = db.OpenRecordset("tblLocations", dbOpenForwardOny)

While not rs.eof
[do something]
wend

rs.close
Set rs = Nothing
Set db = Nothing

In the example able, the recordset object is reused, as opposed to
destroying it and then recreating it.
I would prefer the DAO version. I could use code samples.

:



I'm a bit more versed in DAO (hint). But have some dabbling in ADO. As I
seem to recall, it is possible to reuse a open connection.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top