SaveAsText using external Database

[patriciaxxx]

I have the following code which other experts on the web say works

Dim app As New Access.Application
Set app = CreateObject("Access.application")
app.OpenCurrentDatabase "C:\Documents and Settings\Desktop\New Folder\new.mdb"
app.SaveAsText acForm, "frmCopyMove", "C:\Documents and Settings\Desktop\New Folder\frmCopyMove.txt"

app.Quit

But for me it gives following errors either “cant find the database” or “you cancelled the previous action”

I need to run SaveAsText and LoadFromText from one database and select the target database and folder where text docs are stored.

 

[strongm]

>experts

I'd love to hear those 'experts' explain:

[blue]Dim app As New Access.Application
Set app = CreateObject("Access.application")[/blue]

>“cant find the database”

Are you sure? Or was it perhaps "Microsoft Office Access can't open the user database because it is missing, or opened exclusively by another user"?

Also, where does your macro live? Is it perchance within new.mdb?

 

[patriciaxxx]

Hello strongm

Yes, your right that was the message.
I don't have a macro.
Can you post an example of code that will achieve my goal?

 

[strongm]

Sorry - where does your code live? I'm assuming in a module in new.mdb

Now think long and hard about what might happen if you have some code in new.mdb that says "open new.mdb" ...

 

[patriciaxxx]

Hello strongm

No, the code lives in a different database thats why I posted this thread, the SaveAsText works when you run it from the current database, I need it to work when I target an external database in the example posted it was new.mdb

 

[strongm]

Are you sure that you have got nothing else going on that might be keeping a lock on that database (if Access has a lock on it, there will be a 'new.ldb' file in the same folder as 'new.mdb')?

 

[patriciaxxx]

Hello strongm

Absolutely sure, no lock, nothing going on with that database.

I’m using access 2002 on windows XP pro, I don’t know if that makes a difference. My research on the web tells me the code should work, does and has worked for others (though don’t know on what version of access).

If you have Access 2002 try the code, all I get is error 2001 ”you cancelled the previous operation”. It must need some special piece of missing code to fix the problem but its out of my depth to know what,

If you could post code that works in all Access versions I would be grateful.

 

[strongm]

There's no missing code, as far as I can see. Even though the code you have been given is not quite ideal it should indeed do what you want it to do. The error(s) you are getting suggest something else is going on (a lock on new.mdb being the most obvious one to check). So we've got to figure out what that something is.

 

[patriciaxxx]

Hello strongm

Please don’t shout at me but listeneing to your help I looked again and noticed that very file 'new.ldb'.

I hope this helps you to code me something that works, because it all has me quite confused.

I would have thought that “app.OpenCurrentDatabase…” would open the database and in doing so “new.ldb” would be there. I’m sure when I use OpenCurrentDatabase in other situations that’s the case. Having said that I’ve obviously got something wrong here hope you know how to sort it.

Thank you for sticking with me.

 

[dhookom]

I tried your code with Access 2007 and it works as expected. Have you attempted to compile your code? Was OpenCurrentDatabase() available in Access 2002?

Duane
Hook'D on Access
MS Access MVP

 

[strongm]

>Was OpenCurrentDatabase() available in Access 2002?

Been around since at least Access 97

>it works as expected

Yep, as I said earlier, the code does what it is supposed to do - although good to have confirmation with empirical data.

There has to be something else going on here that we are unaware of. We need more data from Patricia; for example is new.mdb just a test db with the relevant form in it, or is it a genuine production Db (or, at least, copy of such). Does it have macros? What is the security setting she is running Access under? What happens if she tries to open new.mdb up directly in Access (before and after after getting the error)?

 

[patriciaxxx]

Hello strongm

New.mdb is a genuine production Db, well a copy of it to work with. It is not a half blown test db. There are no macros. I am not aware of any security setting Access is running under, that is to say I have set none, on the security tab it says Admin, I don’t know if that’s what you mean, but like I said I haven’t set any security of any kind. I can open the database directly in Access before and after the error occurs without any problem.

Is there a different better way to code this that might overcome whatever the quirk is in access 2002.

 

[strongm]

>whatever the quirk is in access 2002

Hmmm ...

 

[dhookom]

I think Strongm makes a good point about security. Is the folder a trusted location?

Duane
Hook'D on Access
MS Access MVP