Help with MS Access Please

[TinyNinja]

Hello everyone!

I need some help on creating a small program that will automatically compact a MS Access database for me.

I am using MS Access Database 2016 / 365 version.
I am using this driver set = Microsoft Access Driver (*.mdb, *.accdb)
My database has the .accdb tag.
The database is encrypted so I need to be able to open the database with the password and then compact it.

I have found different examples out there but all of them assume the database is not encrypted. I try different things to get the database open with no luck.

Does anyone have any ideas on how I can create this?
I am also open to creating this script in another language like Python.

Thank you in advance for the help!

 

[GriffMG]

You do know this is a forum for VFP not Access?



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TinyNinja]

Yes I know that. I have seen people in the past ask about Access in this forum before. Those posts don't solve what I am trying to figure out.

I want to create a VFP program that will open Access and compact the database for me. I am tired of manually having to open my database and compact it. I want to automate the process.

 

[GriffMG]

Ok that's a bit clearer

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TinyNinja]

I apologize for the confusion. I hope someone can give me a hand.

oAccess = getobject(lserver,[Access.Application.16])
oAccess.CompactRepair

I found something like this but it does not work.

 

[Olaf Doschke]

VFP also has GETOBJECT, but look into the help, it's parameterization does not work this way. The more interesting info to extract here is there is Acces.Application. Are you really astonished about that, there is Word.Application, Excel.Aplication, InternetExplorer.Application, Outlook.Application, and some more.

As far as I see CompactRepair is a method having parameters.
Do you use the debugger? Or the command window?

If not, then begin here Just one line:

oAccess = CreateObject("Access.Application")

Copy this into the command window and execute with ENTER

You might try

oAccess = CreateObject("Access.Application.16")

But that only works, if you have major version 16, that is Access 2016 or 2019, see

https://en.wikipedia.org/wiki/Microsoft_Access#Versions

So you typically go for a common class name, not too specific. Next, copy this:

oAccess.CompactRepair

And then type an opening bracket, only if you type it, and not copy it, you'll see IntelliSense popping up, like this:

And this tells you, what is to be expected: No software can know what you want from it without any specifications, in this case at the minimum you need to specify a file name of a database file.

You likely will not get around a login, if that would be possible, what would the point of a login be, if it can simply be circumvented by programmatically opening a file?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TinyNinja]

I use a mixture of the command window (mostly when testing) and the prg screen.

This is the error I am getting. I'm guessing this is what you mean by not getting around the login?

 

[Olaf Doschke]

Now this is a detail you may ask in an accsss forum here, but if you're sure the file is an access file, the path is correct, then the question is, is anyone connected to that databse? 'cannot be opeened exclusively' may likely mean the same as it means in VFP, when you want to PACK a dbc or dbf, you need exclusive access, so you can only compact a databse file, that's not open by anyone.

Did you specify full paths? Source and Destination file?

Notice: When you automate anything, it has no idea about a current path in VFP, a current path is not a system thing, this isn't DOS, this is a multiprocessing Windows OS, so specify full path names.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TinyNinja]

I am trying to compact a MS Access database on my local PC with no one connecting to it other than me.

I can go over to the Access forums then.

 

[Scott24x7]

In VFP you have to open the file EXLUSIVE with that tag: USE MYDBF.DBF EXCLUSIVE
If your VFP settings default to a non-exclusive mode (which can be set in options), then it may be opening SHARED.
I don't know zip about Access, but it's possible the same kind of thing is true.
You will need to see if you need to somehow gain that file as exclusive within your CompactRepair function?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[TinyNinja]

I'm looking at other ways to open it but it has proven difficult so far.
The CompactRepair does not have anything for exclusive access to the database.

 

[Olaf Doschke]

Have you tried a full path file name? Have you tried with a new empty simple db without any security login?

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

Why not try this:
Pop this in a batch file, run it from the command line, if it works, run the batch file from within VFP

msaccess.exe "c:\myfolder\mydatabase.accdb" /compact

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TinyNinja]

This works just fine. I created a new Access Database will minimal data and the Compact works without a problem when no password is required.
I just need to figure how the best way to get the backup to be the main database. I'm not sure if deleting the original and then renaming the compacted one to the original name is the best way.
I have to keep the database password protected unfortunately.

oAccess = Createobject([Access.Application])
oAccess.CompactRepair(SYS(5)+CURDIR()+[Database1.accdb],SYS(5)+CURDIR()+[Database1_Backup.accdb])
** How to copy Backup over the Original
oAccess.Quit()

This completes very quickly actually.

 

[TinyNinja]

GriffMG
I have created a batch file before and it works fine when no password is present but I'm not sure how to add a password to the script for it to work properly. I've tried different ways to get it to work with no luck.

 

[GriffMG]

You could try the /PWD=myPassword1234 option

So that would be:

msaccess.exe "c:\myfolder\mydatabase.accdb" /compact /pwd=myPassword1234

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TinyNinja]

That did not work for me. I get a login screen prompt when this runs.

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\Database.accdb" /compact /pwd=test123test

 

[GriffMG]

I think if you look here:

https://access-programmers.co.uk/forums/showthread.php?t=176511

You should find more of the available parameters

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TinyNinja]

Thank you everyone for all the help!

 

[Olaf Doschke]

Seems you figured out the last step and perhaps the command line better fits your needs.

I found this and other code, that actually does delete the original mdb/accdb file and replaces it with the compacted one:

http://www.freevbcode.com/ShowCode.asp?ID=1024

So the OLE Server method CompactRepair( is meant to be used that way, it doesn't overwrite the original file.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name