Managing an Access Database from Excel

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" <vezerid@ac.anatolia.edu.gr> wrote in message
news:cuiga7$2f7v$1@ulysses.noc.ntua.gr...
>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" <JunkGeorgeN@msn.com> wrote in message
news:ura#6yFEFHA.1564@TK2MSFTNGP09.phx.gbl...
> > 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" <vezerid@ac.anatolia.edu.gr> wrote in message
> news:cuiga7$2f7v$1@ulysses.noc.ntua.gr...
> >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
> >
> >
>
>