×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help with MS Access Please

Help with MS Access Please

Help with MS Access Please

(OP)
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! :)

RE: Help with MS Access Please

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

Regards

Griff
Keep Smileing

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.

RE: Help with MS Access Please

(OP)
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.

RE: Help with MS Access Please

Ok that's a bit clearer

Regards

Griff
Keep Smileing

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.

RE: Help with MS Access Please

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

CODE --> VFP

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

I found something like this but it does not work.

RE: Help with MS Access Please

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:

CODE

oAccess = CreateObject("Access.Application") 
Copy this into the command window and execute with ENTER

You might try

CODE

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#Ver...

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

CODE

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

RE: Help with MS Access Please

(OP)
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?



RE: Help with MS Access Please

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

RE: Help with MS Access Please

(OP)
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.

RE: Help with MS Access Please

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."hammer

RE: Help with MS Access Please

(OP)
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.

RE: Help with MS Access Please

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

RE: Help with MS Access Please

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 Smileing

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.

RE: Help with MS Access Please

(OP)
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.

CODE --> VFP

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.

RE: Help with MS Access Please

(OP)
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.

RE: Help with MS Access Please

You could try the /PWD=myPassword1234 option

So that would be:

CODE

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

Griff
Keep Smileing

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.

RE: Help with MS Access Please

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

CODE -->

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

RE: Help with MS Access Please

I think if you look here:

https://access-programmers.co.uk/forums/showthread...

You should find more of the available parameters

Regards

Griff
Keep Smileing

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.

RE: Help with MS Access Please

(OP)
Thank you everyone for all the help!

RE: Help with MS Access Please

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

RE: Help with MS Access Please

Access' Application.DBengine has a CompactDatabase method that allows you to provide a password, eg something like:

oAccess.Application.DBEngine.CompactDatabase Datapath, TempFile, False, False, ";PWD=myPassword" 'Compact the file

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close