I am trying to manage an Access 2000 database from Excel 2000. My intention
is to enter Excel data into the "Test" table of Access. The table has been
created and is visible. The online documentation is incomplete because of
the installation and I cannot find what is wrong.

Sub test1()
Dim Acc As Object, RS As Object, DB As Object

Set Acc = CreateObject("Access.Application.9")
On Error Resume Next
Acc.newcurrentdatabase ".\Questionnaires.mdb"
Acc.opencurrentdatabase
On Error GoTo 0
Set DB = Acc.currentDB
Debug.Print DB.Name
'Set RS = DB.openrecordset("Test")
End Sub


Questions:
1. I have put the On Error Resume Next because once the database is opened
the next line produces an error. How can I test if Access has already
started from the first statement?
2. The tricky part though is when I try to connect to the specific table by
first connecting to the DB:
Set DB = Acc.CurrentDB. Obviously this object assignment is not working
because in the next line I get Runtime 91, Object Variable not Set.

If I manage to make the commented line work (Set RS =...) then I think I can
proceed from that point on. But what is going wrong?

TIA
Kostis Vezerides

Acc.newcurrentdatabase ".\Questionnaires.mdb"
1) ".\Questionnaires.mdb" should be "the full name of the database file,
including path name ..." (per Help file entry). This doesn't look like a
full path to me, so I doubt this line is doing anything. which leads us to
#2....

2) Per Help file entry for the NewCurrentDatabase method, this method
creates a *new* database with the specified filename/path. "If filename
already exists, an error occurs." Since you are trying to open a recordset
from this db, I assume it already exists. If so, you don't want to use this
command. OpenCurrentDatabase is probably all you need, but it needs a
path/filename argument. (untested aircode):
Set Acc = CreateObject("Access.Application.9")
Acc.OpenCurrentDatabase "C:\MyDocuments\Questionnaires.mdb"
Set DB = Acc.CurrentDB
'.....
'....Do whatever you are going to do with the Access object
'.....
Set DB = Nothing
Acc.CloseCurrentDatabase
Set Acc = Nothing

With your existing code, by the time you get to "Set DB = Acc.CurrentDB", I
doubt that there is a current db: you haven't successfully created a new db
or opened an existing one. That's why you get "Object Variable not Set" when
you try to print DB.Name.
--
George Nicholson

Remove 'Junk' from return address.


"Kostis Vezeridis" wrote in message
...
I am trying to manage an Access 2000 database from Excel 2000. My intention
is to enter Excel data into the "Test" table of Access. The table has been
created and is visible. The online documentation is incomplete because of
the installation and I cannot find what is wrong.

Sub test1()
Dim Acc As Object, RS As Object, DB As Object

Set Acc = CreateObject("Access.Application.9")
On Error Resume Next
Acc.newcurrentdatabase ".\Questionnaires.mdb"
Acc.opencurrentdatabase
On Error GoTo 0
Set DB = Acc.currentDB
Debug.Print DB.Name
'Set RS = DB.openrecordset("Test")
End Sub


Questions:
1. I have put the On Error Resume Next because once the database is opened
the next line produces an error. How can I test if Access has already
started from the first statement?
2. The tricky part though is when I try to connect to the specific table
by
first connecting to the DB:
Set DB = Acc.CurrentDB. Obviously this object assignment is not working
because in the next line I get Runtime 91, Object Variable not Set.

If I manage to make the commented line work (Set RS =...) then I think I
can
proceed from that point on. But what is going wrong?

TIA
Kostis Vezerides

Thank you so much. It worked.

Kostis Vezerides


"George Nicholson" wrote in message
...
Acc.newcurrentdatabase ".\Questionnaires.mdb"
1) ".\Questionnaires.mdb" should be "the full name of the database file,
including path name ..." (per Help file entry). This doesn't look like a
full path to me, so I doubt this line is doing anything. which leads us to
#2....

2) Per Help file entry for the NewCurrentDatabase method, this method
creates a *new* database with the specified filename/path. "If filename
already exists, an error occurs." Since you are trying to open a
recordset
from this db, I assume it already exists. If so, you don't want to use
this
command. OpenCurrentDatabase is probably all you need, but it needs a
path/filename argument. (untested aircode):
Set Acc = CreateObject("Access.Application.9")
Acc.OpenCurrentDatabase "C:\MyDocuments\Questionnaires.mdb"
Set DB = Acc.CurrentDB
'.....
'....Do whatever you are going to do with the Access object
'.....
Set DB = Nothing
Acc.CloseCurrentDatabase
Set Acc = Nothing

With your existing code, by the time you get to "Set DB = Acc.CurrentDB",
I
doubt that there is a current db: you haven't successfully created a new
db
or opened an existing one. That's why you get "Object Variable not Set"
when
you try to print DB.Name.
--
George Nicholson

Remove 'Junk' from return address.


"Kostis Vezeridis" wrote in message
...
I am trying to manage an Access 2000 database from Excel 2000. My
intention
is to enter Excel data into the "Test" table of Access. The table has
been
created and is visible. The online documentation is incomplete because
of
the installation and I cannot find what is wrong.

Sub test1()
Dim Acc As Object, RS As Object, DB As Object

Set Acc = CreateObject("Access.Application.9")
On Error Resume Next
Acc.newcurrentdatabase ".\Questionnaires.mdb"
Acc.opencurrentdatabase
On Error GoTo 0
Set DB = Acc.currentDB
Debug.Print DB.Name
'Set RS = DB.openrecordset("Test")
End Sub


Questions:
1. I have put the On Error Resume Next because once the database is
opened
the next line produces an error. How can I test if Access has already
started from the first statement?
2. The tricky part though is when I try to connect to the specific table
by
first connecting to the DB:
Set DB = Acc.CurrentDB. Obviously this object assignment is not working
because in the next line I get Runtime 91, Object Variable not Set.

If I manage to make the commented line work (Set RS =...) then I think I
can
proceed from that point on. But what is going wrong?

TIA
Kostis Vezerides