Open Connection

  • Thread starter Thread starter Lars Munch
  • Start date Start date
L

Lars Munch

In an Access database I am trying to use a Visual Basic
module to fill in some calculated values into a table. I
understand quite well, that if I were using Visual Basic
under other circumstances, I would have to tell Visual
Basic which database to operate on, the complete path and
filename of the database, and I would have to open a
connection to the database. However, in this case the
Visual Basic code is a module within a certain Access
application; - shouldn't this be a much more simple
situation? In this case I had expected the connection to
that certain database to be open as default.

Now it seems to be a little more complicated than that.
If I don't open the connection first, I get runtime error
3709, when I try to open the recordset. I have looked
for solutions in the "help" function of Visual Basic, and
I have tried some of the solutions from the code examples
of this "help" function, but then I only get other error
codes. In "help" I have not found any solutions to this
simple problem. Can anyone help me?

Somewhere in "help" it is said that "Open connection" is
used to establish connection to an external database.
When is a database said to be external?
 
Lars,

You can refer to the curent database in VBA like this:

Using DAO:
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = Currentdb
Set rs = db.OpenRecordset("tbl_Name")

or Using ADO:
Dim cnn as ADODB.Connection
Dim rst as ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = cnn.Execute("SELECT * FROM tbl_Name")

HTH,
Josh
 
Assuming you are talking about the ADO Connection, then (in A2K & AXP)

CurrentProject.Connection

returns a reference to the current ActiveX Data Objects (ADO) Connection
object and its related properties.

Check Access VB for the Connection Property of the CurrentProject object.
 
Lars,

I gather you are using DAO, Correct?
If you don't specify those options, it will attempt to open the recordset as
a table or dynaset(for linked tables or queries) with optimistic locking.
If the table, for some reason, is not updateable, then you can't open a
dynaset type recordset. Try this:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = Currentdb
Set rs = db.OpenRecordset("tbl_Name", dbOpenTable)

Read 'OpenRecordset Method' in Access help for more options. Error 91 means
the rs variable did not get set, in other words the line that begins with
'Set rs' caused an error.

HTH,
Josh
 
Back
Top