Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close CurrentDatabase
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close CurrentDatabase
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 "Hydra" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close CurrentDatabase
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? "Doug Glancy" wrote: 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 "Hydra" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close CurrentDatabase
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" wrote: 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 "Hydra" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close CurrentDatabase
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 "Hydra" wrote in message ... 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? "Doug Glancy" wrote: 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 "Hydra" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open/Close CurrentDatabase
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. "Hydra" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open & close event | Excel Programming | |||
OPen and close file | Excel Programming | |||
if its open...close it | Excel Programming | |||
Open then Close | Excel Programming | |||
On Open/On Close | Excel Programming |