Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kostis Vezeridis
 
Posts: n/a
Default 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


  #2   Report Post  
George Nicholson
 
Posts: n/a
Default

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




  #3   Report Post  
Kostis Vezeridis
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Open Access Database under and Excel window using a Macro BMSpell Excel Worksheet Functions 1 January 8th 05 06:32 PM
Query a Access database that has a module from Excel Oggie Excel Discussion (Misc queries) 1 January 4th 05 09:43 AM
How to use a Access Query that as a parameter into Excel database query Karen Middleton Excel Discussion (Misc queries) 1 December 13th 04 08:54 PM
Excel aficionado wants to learn Access Hari Excel Discussion (Misc queries) 0 December 3rd 04 06:45 AM


All times are GMT +1. The time now is 02:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"