Solved Unable to OpenDatabase on CD Drive (Windows 10) (1 Viewer)

[dw85745]

I'm trying to get an Access Table stored in an Access Database on a CD Drive with
the intent of copying that Table to an Access Database on my Hard Drive.
I've tried both of the following, both Fail to return a dbName (error 3051)

'orig   Set dbName = DBEngine.Workspaces(0).OpenDatabase(GetPath(PathDB), True)
   Set dbName = DBEngine.OpenDatabase(GetPath(PathDB))

Thanks

 

[Pat Hartman]

What is the contents of PathDB? Aren't you just extracting the path part of the name? What does GetPath(PathDB) return?

 

[Gasman]

Why not just copy the db to hard disk and then extract?

 

[dw85745]

Thanks both for responding.
1) GetPath returns the path to the DB (here: E:\MyDir\MyDB.mdb)
2) I've got about 50 CD's , I want to copy tables from.
So easiest just to put in CD, run my program, then swap out CD.
MOST IMPORTANTLY, why is it failing?

 

[Gasman]

No idea, but 101 debugging for me would be what I asked. If that works, then look further.

 

[theDBguy]

MOST IMPORTANTLY, why is it failing?

My best guess as to why it's falling is because a CD is basically read only. If you were using a CDRW, it might work.

 

[dw85745]

Gasman:
Program already works if the two DB's reside on the same drive, but different folders. Haven't tested if hard disk
drive letter is different. Will do now.

TEST Works fine if ON Same HD, two different drives.

theDBGuy:
Agree CD is read only. But that, IMHO, should not have anything to do with OpenDatabase.. If I recall there is a switch for
ReadOnly (I know OpenRecordset has one), so will give it a try.

OpenDatabase, ReadOnly Param does nothing. Fails to open on CD.
GUESSING the ReadOnly Param is for the DB itself, NOT the device it resides on.
-----------------------------------------
i THINK YOUR RIGHT ON THE READONLY. Is there any workaround, or switches to over ride?

 

[Gasman]

If you open a DB there should be a lockfile, Yes?
You would be unable to create a lockfile on a read only drive?

If you open a DB exclusive, is a lockfile still present?

 

[dw85745]

Gasman:
OWE YOU ONE. That worked.
Did some reading on lock files, so learned something too., (FWIW, thought I had tried making exclusive, but guess Not).

 

[CJ_London]

why not just use sql? At it's simplest

SELECT * FROM tblName INTO localtable FROM tname IN '" & pathDB & "'"

Or use the linked table manager - the lock file will only appear if you open the linked table so then just use ctrl-c/ctrl-v to copy structure and data to your local table

 

[dw85745]

CJ_London:
That is the exact SQL I am using.
However to execute that SQL statement, I'm using> dbDest.Execute SQL
which I believe requires the DB to be open. Otherwise how does one:

1) Execute the SQL
2) The SQL known which destination database I'm referring to IF I have more than one in a folder (directory).

 

[The_Doc_Man]

If the DB isn't set for open/exclusive, it will try (and fail) to create a lock file on the CD drive. That failure is most likely your problem. But what I wonder is why you don't try to build a script that, when you launch it, searches for the .MDB or .ACCDB file. If the directory structure is more or less fixed, the search would be trivial. How much of the path and the DB name can you predict? A batch script could EASILY do at least some of the mechanical parts of this for you. For that matter, you could program this in VBA with just a little bit of interaction for when you load and unload a CD, to let it know to continue with the process.

 

[dw85745]

Thanks to ALL for responding.

The_Doc_Man::
1)

If the DB isn't set for open/exclusive, it will try (and fail) to create a lock file on the CD drive. That failure is most likely your problem.

CJ_London offered this up, and my ability to use the CD now confirmed.

2) I am using VB code to do the heavy work (which includes opening the DB to execute the SQL, UNLESS
there is way (???) to execute the SQL without opening the DB ).

 

[The_Doc_Man]

UNLESS
there is way (???) to execute the SQL without opening the DB ).

Since the SQL facility will be doing the move and it requires information only available by opening the DB, I would say "NO" on that idea.

 

[dw85745]

Just ran into another problem.
The CD database is opening in exclusive mode, and is finding the table of interest.
HOWEVER, the copying to the database on the HD, appears to be failing with a 3078 error.
SO MY GUESS is:
even though the DB's are on different drives, the fact ACCESS is exclusive prevents copying from the CD.

My Rant > @#$%^&!

 

[The_Doc_Man]

For future reference, don't just quote an error number. Tell us that error 3078 is "Cannot find table..."

It wasn't clear to me before. Are you actually copying TO a CD-resident DB as well as copying FROM a different CD-resident DB?

This is DEFINITELY a case where I would build the DB in my local machine and not try to copy it to the CD drive until there is nothing else you want to do with the destination DB, i.e. it is as full as it is going to ever get. Trying to think of the actual data flow in my head, I see this as a nightmare on a CD. Is it a WORM drive or a CDRW drive?

 

[dw85745]

UPDATE
Working as it should. (My Error as forgot to change which DB in one of my test code line.)

ORIGINAL
The Source DB resides on a CD. The CD drive device is CDRW.
The Destination DB is on the HD (hard Drive).
What my script does is Open Each Database (CD is opened exclusive, HD is open shared).
Then a check is done to confirm the table being copied does NOT reside in the Destination DB.
If Not, the table is copied from the Source DB, to the Destination DB.
(As previously indicated this works fine using a hard drive).
Per ALL the help this thread, opening the Source DB (on CD) exclusive, is letting me get to (read) the Source DB.