[Q] Cannot open database ". (1 Viewer)

bigal.nz · Jul 25, 2017

Hi,

I have a query which works fine for some records and not others. It is a very simple query:

SELECT * FROM MAS WHERE systemNo = 'B123456'

The records that don't work the error is:

Runtime Error 3049
Cannot Open Database ". It may not be a database that your application recognises

To troubleshoot I tried to select the record by its native primary key (instead of systemNo) and it worked, so I figured the system key for that record is somehow corrupt and tried to retype it in for that record but the database is currently in use - will try this later.

Yes the database is split.

Any other ideas?

Cheers

-Al

Galaxiom · Jul 25, 2017

Access uses double quotes as string delimiters unless they are nested in an SQL string in VBA.

Tell us more about how you run this query.

bigal.nz · Jul 25, 2017

Galaxiom said:
Access uses double quotes as string delimiters unless they are nested in an SQL string in VBA.

Tell us more about how you run this query.

Hi

Yes it is in VBA in which case the whole statement is in " " with the string inside '' but for debugging purposes I was running the query in the query window in which case the string was just in ''

Galaxiom · Jul 25, 2017

Sounds like corruption. Import everything to a new database.

Try rebuilding the query. If it still fails, recreate the table and insert the records from the old table. The manually insert the missing record.

isladogs · Jul 25, 2017

Access uses double quotes as string delimiters unless they are nested in an SQL string in VBA.

Code:

SELECT * FROM MAS WHERE systemNo = 'B123456'

You can use single or double quotes in the query designer when a specific text value is used as the filter

In the VBE, single quotes would also work.
Alternatively it would need double double quotes like this in the VBE:

Code:

"SELECT * FROM MAS WHERE systemNo = ""B123456"";"

bigal.nz · Jul 25, 2017

Galaxiom said:
Sounds like corruption. Import everything to a new database.

Try rebuilding the query. If it still fails, recreate the table and insert the records from the old table. The manually insert the missing record.

Is this process easy? There are quite a few tables, reports and queries. Some of the tables are linked one to many so if I delete the problem record I would end up with orphans on other tables.

isladogs · Jul 25, 2017

Its not difficult but strongly recommend you make a backup first.
Suggest you try rebuilding the query and if necessary the table in the existing database first.
If still no luck, then import all items into a new 'clean' db.

Or if you want, upload the relevant parts of your db so somebody can have a look at it

bigal.nz · Jul 25, 2017

ridders said:
Its not difficult but strongly recommend you make a backup first.
Suggest you try rebuilding the query and if necessary the table in the existing database first.
If still no luck, then import all items into a new 'clean' db.

Or if you want, upload the relevant parts of your db so somebody can have a look at it

Can you rebuild just one table? How?

isladogs · Jul 25, 2017

Yes

Rename your existing table (Table1?) with suffix BKP e.g. Table1BKP

Create a new table (e..g. Table1) with exactly the same fields & datatypes.
Make sure the PK field is also set as before

Now import all records from the BKP table using an append query

Then test if its solved your problem
If it has, delete the BKP table

If not, try again using a new blank db as explained previously

bigal.nz · Jul 25, 2017

ridders said:
Yes

Rename your existing table (Table1?) with suffix BKP e.g. Table1BKP

Create a new table (e..g. Table1) with exactly the same fields & datatypes.
Make sure the PK field is also set as before

Now import all records from the BKP table using an append query

Then test if its solved your problem
If it has, delete the BKP table

If not, try again using a new blank db as explained previously

Good to know.

I finally got everyone out for a C&R - which seems to have fixed things so far. Fingers crossed.

Galaxiom · Jul 25, 2017

ridders said:
Rename your existing table (Table1?) with suffix BKP e.g. Table1BKP

This can be fraught. By default Access has AutoNameCorrect turned on. This propagates the new names of objects to other dependent objects. So any queries based on the table will have the name changed in the query next time it runs.

Either turn NAC off before changing any names or create a new table with a new name, move the data into it, delete the old table and rename the new one to the original name.

In any case where there has been corruption, it is a good idea to import everything to a new database. It is a trivial process. Indeed it is a good idea to do it from time to time even without corruption because it cleans out residual junk.

[Q] Cannot open database ". (1 Viewer)

bigal.nz

Registered User.

Galaxiom

Super Moderator

bigal.nz

Registered User.

Galaxiom

Super Moderator

isladogs

MVP / VIP

bigal.nz

Registered User.

isladogs

MVP / VIP

bigal.nz

Registered User.

isladogs

MVP / VIP

bigal.nz

Registered User.

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread