Open/Close CurrentDatabase

[Hydra]

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.CloseCurrentDatabase

This throws an error that says the expression refers to an object that is
closed or does not exist.

------------------

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.OpenCurrentDatabase ("DataCheck")

Throws an error that says the object is missing or open by another user.

-------------------

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.NewCurrentDatabase ("DataCheck")

Creates a new Database Called "DataCheck" and displays it, Provided
DataCheck does not already exist. If it does exist it throws an error saying
there is an existing database with that name.

-------------------------------------------------------------------

I want to create this file and populate it with data from an Excel sheet,
But if it already exists then I want to kill it and create a new one from
scratch because the columns of data in the Spreadsheet may change.

Or at very least, open the existing file and empty all the data before I
repopulate it.

 

[Doug Glancy]

Hydra,

Your first error occurs because no database is open. This is because you
just opened Access programmatically and haven't yet opened a database.

Your second error occurs because you don't specify the full path for the
database, e.g., "C:\Users\doug\Documents\DataCheck"

I think you want something like this:

Sub test()
Dim appAccess As Access.Application

If Dir("C:\Users\doug\Documents\DataCheck.mdb") <> "" Then
Kill "C:\Users\doug\Documents\DataCheck.mdb"
End If
Set appAccess = CreateObject("Access.Application")
With appAccess
.NewCurrentDatabase ("C:\Users\doug\Documents\DataCheck.mdb")
.Visible = True
'*** do your stuff here
.Quit
End With
Set appAccess = Nothing
End Sub

hth,

Doug

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.CloseCurrentDatabase

This throws an error that says the expression refers to an object that is
closed or does not exist.

------------------

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.OpenCurrentDatabase ("DataCheck")

Throws an error that says the object is missing or open by another user.

-------------------

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
appAccess.NewCurrentDatabase ("DataCheck")

Creates a new Database Called "DataCheck" and displays it, Provided
DataCheck does not already exist. If it does exist it throws an error
saying
there is an existing database with that name.

-------------------------------------------------------------------

I want to create this file and populate it with data from an Excel sheet,
But if it already exists then I want to kill it and create a new one from
scratch because the columns of data in the Spreadsheet may change.

Or at very least, open the existing file and empty all the data before I
repopulate it.











__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4157 (20090615) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4160 (20090616) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Hydra]

I didn't explain well.

I ran the third part first, and created the database "DataCheck". so my
problem is that having created it, I can neither open nor close it. You are
telling me that even though I created it, it isn't open? that I still need
the full path in order to open it?

It doesn't create the database in the current directory and I still need the
full path in order to open it, or that it does not default to looking in the
current directory? I won't necessarily know where this will be located, so I
was thinking that I didn't need the full path as long as everything was in
the current directory.

How do I do this so it is path independent?

 

[Hydra]

So, I assumed a known directory and used your code.

.NewCurrentDatabase ("C:\DataCheck.mdb")

Throws an error that says there is already a database withthat name. give
the new database a unique name.


There is no database by that name. I erased all such databases, first. Then
I tried with a new name and got the same error.

 

[Doug Glancy]

Hydra,

"You are telling me that even though I created it, it isn't open? that I
still need the full path in order to open it?" No, I'm not.

You should try running it in the order I sent you. Try stepping through it
using F8, so you can see the file being deleted, if it's there, and then a
new version being created.
Without seeing your code I can't try to tell you why it's not working.

To solve the default path problem I'd add code that determines the directory
of the workbook with the code. So here's the modified code:

Sub test()
Dim appAccess As Access.Application
Dim strCurrentPath As String

strCurrentPath = ThisWorkbook.Path
If Dir(strCurrentPath & "\DataCheck.mdb") <> "" Then
Kill strCurrentPath & "\DataCheck.mdb"
End If
Set appAccess = CreateObject("Access.Application")
With appAccess
.NewCurrentDatabase (strCurrentPath & "\DataCheck.mdb")
.Visible = True
'*** do your stuff here
.Quit
End With
Set appAccess = Nothing
End Sub

hth,

Doug

 

[Patrick Molloy]

do you really want to open Access and then open the database?
Usually one would only want to extract/edit data, and you can do this via
ADODB ... connecting and executing a command or a SQL select statement o
populate a recordset, which can then be used to populate a table, pivotcache
or whatever.