Problems with QueryDefs and Replication (Access 2000 DB)
Hi,
My company took over a small Access database for a small studio. They needed the ability to take their database on the road with them, so we decided the easiest thing to do would be to create a replica of the database and then sync it with the master when they return from the road. However, we started running into 'cannot update, database is read only' errors when opening some of the forms. The debugger would always stick on a line containing a querydef. For example:
CurrentDb.QueryDefs("qryCurrentStudioEntries").SQL = _
"SELECT * " & _
"FROM Entries " & _
"WHERE [Studio ID]=0 " & _
"ORDER BY [Age Division ID], [Category ID], Name"
This database is written in DAO, and no one in my organization is really familiar with it as everyone uses ADO. Is there a way around QueryDefs? It seems that they are used to place data in a temporary file, which requires write access to the database. A replication cannot be written to, so I was wondering if there is a way to just store the data to memory using some sort of array or variable?
[1148 byte] By [
outis] at [2007-11-11 9:56:48]
# 1 Re: Problems with QueryDefs and Replication (Access 2000 DB)
Do you know what this QueryDef is used for? Do you know why the SQL is being changed?
Based upon the line of code I can't see why it would be changed because there doesn't appear to be anything in the statement itself that contains dynamic information. Perhaps it's reused and the SQL is also changed elsewhere in the database?
# 2 Re: Problems with QueryDefs and Replication (Access 2000 DB)
I'm having trouble understanding that too. Basically, that QueryDef is just supposed to populate a list that fills a text box on a form. I am not able to contact the guy that originally wrote the database unfortunately, and I really don't understand why he coded it this way.
Private Sub Form_Open(Cancel As Integer)
' check if we already have a filter
If IsNull(Me.OpenArgs) Or "" = Me.OpenArgs Then
' start with no selected routine
Me.Filter = "[Studio ID]=0"
Me.FilterOn = True
' filter the entries list box for the current studio
CurrentDb.QueryDefs("qryCurrentStudioEntries").SQL = _
"SELECT * " & _
"FROM Entries " & _
"WHERE [Studio ID]=0 " & _
"ORDER BY [Age Division ID], [Category ID], Name"
' start with no selected studio
Me.cbStudioID = 0
Else
' use the old find first trickeroo
Me.Recordset.FindFirst ("ID=" & Me.OpenArgs)
' ok, now select the ******* studio
Me.cbStudioID = Me.[Studio ID]
End If
' update the orders and the scores
UpdateOrderScores
' refresh this mofo
Me.Refresh
' disable the comp changer
Me.frmCompetitionChanger.Form.EnableChanger
End Sub
outis at 2007-11-11 23:44:39 >
# 3 Re: Problems with QueryDefs and Replication (Access 2000 DB)
Did you look at the QueryDef in the designer to see if it's any different than the SQL in the code?
Were you able to determine whether the QueryDef is used anywhere else?
# 4 Re: Problems with QueryDefs and Replication (Access 2000 DB)
What is going on here is that the combo box control, cbStudioID, has it's rowsource set to the saved query named qryCurrentStudioEntries. Now to make the list of entries that shows up in the combo box more dynamic to the info on the form he has chosen to alter the SQL stored in that query. The QueryDeffs collection stores all saved queries as QueryDeff objects. One of the properties of the QueryDeff object is the SQL property. This holds the saved SQL statment. By changing this value and then refreshing the control the items listed in the control would change.
One of the problems you could have is that the query called qryCurrentStudioEntries was not carried over to the replicated database.
Another problem could be that the SQL is not valid and therefore is causing an error when you try to assign it to the SQL property. If you look at the items in the ORDER BY clause of the SQL statement, you will notice and Item is called Name. Name is used by access in a lot of objects and can trigger an error if you give a field the name of "Name". You might try adding brackets around it and see if that clears up your error. I.E. ORDER BY [Age Division ID], [Category ID], [Name]
Also another way to do this is to replace the combo box controls row source with the actual SQL and don't bother modifying the QueryDeff object. Like This:
Private Sub Form_Open(Cancel As Integer)
' check if we already have a filter
If IsNull(Me.OpenArgs) Or "" = Me.OpenArgs Then
' start with no selected routine
Me.Filter = "[Studio ID]=0"
Me.FilterOn = True
' filter the entries list box for the current studio
Me.cbStudioID.RowSource = "SELECT * FROM Entries WHERE [Studio ID]=0 ORDER BY [Age Division ID], [Category ID], [Name]"
' start with no selected studio
Me.cbStudioID = 0
Else
' use the old find first trickeroo
Me.Recordset.FindFirst ("ID=" & Me.OpenArgs)
' ok, now select the ******* studio
Me.cbStudioID = Me.[Studio ID]
End If
' update the orders and the scores
UpdateOrderScores
' refresh this mofo
Me.Refresh
' disable the comp changer
Me.frmCompetitionChanger.Form.EnableChanger
End Sub
# 5 Re: Problems with QueryDefs and Replication (Access 2000 DB)
Thanks everyone for your quick help on this.
Ron, that code you pasted worked. Just bypassed the querydefs and replication works fine now. Thanks a lot!
outis at 2007-11-11 23:47:33 >
