Question can't open queries directly from split database

[wiklendt]

hi everyone,

i've recently created my very first split database (yay for me). everything in the FE works as expected however, i can't directly open any queries (even though almost all the forms/reports which rely on said queries work fine). when i try to open ANY of the queries (either directly, or via a button on a form) i get the following error message.

" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long

it's not SUPER important to be able to open these queries outright (since they are working within the forms/reports) BUT i just wanted to make sure it wasn't something terribly wrong in my DB.

...yes, i have compact and repaired both the front end and back end.

could the fact that there is a back end path involved be screwing around with the "not too long" bit of the query opening error?

 

[wiklendt]

actually, it is important, coz i can't access the query design if i want to make changes in either the query or from forms...!! eeek!

 

[Galaxiom]

Sounds like you converted the Front End to an MDE or ACCDE.
If so I hope you still have the mdb or accdb file.

 

[wiklendt]

Sounds like you converted the Front End to an MDE or ACCDE.
If so I hope you still have the mdb or accdb file.

no, what i did was copy the mdb, then deleted all the tables from the "FE", and deleted all the non-table objects from the "BE".

i then used this code to relink the tables from the BE to the FE.

and yes, 'backup' is my middle name

now - how to fix the query problem? is this usualy in a split DB? should i just try to split it again and hope for the best? i even tried copying the queries directly over from my last non-split version into the split FE, but same error.

 

[Galaxiom]

Actually I just checked and queries can be edited in an mde anyway.
Queries certainly should remain editable in a split db.

Maybe it did get corrupted somehow. I would try doing it again.

 

[DCrake]

I have commented on this many times that no matter what type of Access application you are writing be it a stand alone with a few tables or a massive application in a multi user environment. You should always start with a split database.

Certain things are possible in unsplit mdbs that ar not available in split mdbs. And it's only when you decide to split it does it rear its ugly head. Ok making amendments to tables/fields is long winded but "Fail to Prepare and Prepare to Fail".

David

 

[AJordan]

Not sure exactly how you split your database or what version of Access your are working with. However, if you have your original unsplit database try this:
Open your "Unsplit DB"
Open a New Access Database
Copy all of your tables in unsplit
Paste your tables in your new access database (Split_BE)
Save "Split_BE" and Close

Within your original Delete only the Tables
Now Link all of the tables within the new db you just created (Split_BE)

This should work on a basic database (whatever that means). However since I don't know how your tables are set up, if your using macros or VB. It would be really hard to problem solve.

 

[gemma-the-husky]

wik

at the risk of being daft...

the queries are in the front end, not the back end, aren't they

 

[wiklendt]

wik

at the risk of being daft...

the queries are in the front end, not the back end, aren't they

yes, they are. looks like i just have to try again with splitting. i can do it a couple different ways. i can try the inbuilt splitter, or using a new DB for the BE as suggested by AJordan

(my access version is 2007 - it's in my sig and i described my splitting method in post #4 - made two copies of the DB, then deleted tables from one, other objects in other, then linked them via the access tools for linking external data.)

DCrake, i take your point in having the app split right from the get-go, and have read this a lot in many posts (probably all yours LOL), but i chose the option of not splitting until later - maybe now that i'm a more experienced Access DB user i'll do that for my next DB, but i didn't feel confident enough with this one when i first started.

it's ok everyone, i'll just try again. i'm not looking to create and mde, i'm not looking to 'secure' this DB - it's only an ordering DB for lab consumables - no Intellectual Property there! (and i am fine with leaving the DB essentially "open source")

thanks for your responses, i just really needed to know whether i was dealing with a bug/corruption or if i'd missed some option or didn't tick some box in the splitting process. your answers have clarified that i just got a bad egg split this time, so i'll just try again

and, ultimately, if it has to remain unsplit, then i can, there will only REALLY be one user in this DB at any one time (i know, i know, i've read about even just one user corrupting data in a non-split DB)

actually, i just realised i'll have access to version 2000/2003 at work, so i can even try splitting there.

thanks y'all.

 

[wiklendt]

oops, and when i said in my post #4 that i used "this" code to re-link the tables, i meant the namliam post here. but i have used the access 'external data' method for another DB just recently, so that's why i confused the two methods here...

 

[wiklendt]

ok. here's the weird thing. i just got back to work after some time off. we have access 2003 here. i split the database in 2003. queries work fine etc.

i copy the exact same split db to my laptop, running Access 2007, and i get the query error.

so it's an access 2007 thing. at least it works in 2003 coz that's what we have here at work and this is where it will be used - however, i hope they don't want to upgrade too soon to access2007!!

 

[wiklendt]

the error returned when i try to access a query from a button on a form is

when i hit debug, it takes me to the red line in the code below. HOWEVER, i know it's not an issue in the code but an issue in access 2007 with opening the queries (there is only one query that is invoked from a form in this DB).

Private Sub cmdOpenQryALL_Click()
    
    Dim strDoc As String
    
    strDoc = "qryBatches"
    
[COLOR=Red]    DoCmd.OpenQuery strDoc, , acReadOnly
[/COLOR]
End Sub

if someone else has AC2007, could they see if this all fails on your comp too? thanks. (the name "working!" was in reference to something else completely unrelated that i got excited about..., and technically, it works in access 2003...)

 

[AJordan]

I could be completely wrong but that action should look more like this:

Private Sub cmdOpenQryALL_Click()
On Error GoTo Err_cmdOpenQryALL_Click
Dim strDoc As String

strDoc = "qryBatches"

DoCmd.OpenQuery strDoc, , acReadOnly

Exit_mdOpenQryALL_Click:
Exit Sub

Err_mdOpenQryALL_Click:
MsgBox Err.Description
Resume Exit_Dim strDoc As String

strDoc = "qryBatches"

DoCmd.OpenQuery strDoc, , acReadOnly


End Sub

 

[Galaxiom]

I could be completely wrong but that action should look more like this:

Private Sub cmdOpenQryALL_Click()
On Error GoTo Err_cmdOpenQryALL_Click
Dim strDoc As String

strDoc = "qryBatches"

DoCmd.OpenQuery strDoc, , acReadOnly

Exit_mdOpenQryALL_Click:
Exit Sub

Err_mdOpenQryALL_Click:
MsgBox Err.Description
Resume Exit_Dim strDoc As String

strDoc = "qryBatches"

DoCmd.OpenQuery strDoc, , acReadOnly


End Sub

You are completely wrong.
Presumably there are some unintended lines in your post.

Anyway it appears you were just adding error handling. That isn't the issue here because we are not in a Runtime only environment so the error is reported without this.

Actually the standard error handling in Access is rather crappy. Better to use a scheme that includes the name of the procedure and object. MZ-Tools has a script facility to easily add this information.

(No developer should be working without the free wonder plugin MZ-Tools)

BTW IMO setting a variable to use in the command is unnecessary obfuscation. This would do the job except for error handling.

Private Sub cmdOpenQryALL_Click()
DoCmd.OpenQuery "qryBatches", , acReadOnly
End Sub

 

[wiklendt]

besides which, it's not even a code error. it's just an access bug (i presume). if i double click on any query directly, it has the same error (except without the error code 3125)

has any one seen this before? i should google it and see if microsoft has a hotfix or something....

 

[wiklendt]

google is my friend. the culprit was a folder in my folder structure which had a single quotation mark in the folder name ("'s"). as soon as i got rid of it the queries opened like magic

http://bytes.com/topic/access/answers/772840-getting-error-not-valid-name-error-3125-a