Creating a QueryDef object results in runtime error 3027 1

[inahs2k]

Hi everyone,

I am new to VB/VBA so kindly bear with me if my question sounds naive.

I am trying to connect to a Access 2002 Database from an Excel 2002 worksheet macro. I think I am able to open the database fine, but when I try to create a QueryDef object in order to run some queries, I keep getting a Runtime error. It says "Runtime Error: 3027 Cannot update. Database or Object is read-only".

Here is the piece of code that leads to the error.

Sub loadDB()
    Dim db As Database
    Dim recSet As Recordset
    Dim query As String, qDef As QueryDef
    
    Set db = DBEngine.Workspaces(0).OpenDatabase( _
            "C:\MyApps\Access\DBConnect\Company.mdb", False, True)
    
    MsgBox "Database opened successfully..."
    query = "SELECT * from tblCompany where City = 'London'"
    Set qDef = db.CreateQueryDef("shopNameQuery", query)
    Set recSet = qDef.OpenRecordset(dbOpenForwardOnly, , dbReadOnly)
    
    Do While Not recSet.EOF
        Debug.Print recSet.Fields("Company Name")
    Loop
        
    db.Close
      
 End Sub

Now I have 2 questions. One, how do you check to see if you have a successful connection to the Database. Second, why am I getting this error and what can I do to overcome it.

Thanks a lot in advance for your help.

 

[jrbarnett]

Hi,

To answer your second question first, do you have a query named "shopNameQuery" stored in the database? If so, try changing the "shopNameQuery" to "" if you have no need to store it.

I would also put a
recSet.MoveNext
line just before the end of the Do While loop to move through to the next records, otherwise this will cause an infinite loop (and will only finish if there is only one record).
and recSet.Close after the end of the loop, for tidying up purposes.

John

 

[inahs2k]

Hi John,

Thanks very much. It worked. Now the macro runs fine and prints out the results. Also I incorporated your suggestions about MoveNext and Closing the record set. Thanks once again.

I would really be grateful if you could also tell me how to check to see if we have a successful connection to the Database (my first question in the previous post).

TIA,
Shahnaz.

 

[jrbarnett]

Hi,

The only way of telling if you have a successfully opened database connection is to check for error 3024 immediately after the database loads. I have written a version of your code with a few changes, implementing my previous suggestions as well as an error handler. Obviously if you have an error handler elsewhere that this uses then this could be incorporated into it.

In fact, if you are opening a recordset of a select query, then you don't need the querydef either. Thus it can be simplified to the following (including the error handler):

Sub loadDB()
    Dim db As Database
    Dim recSet As Recordset
    Dim query As String
    
    On Error Goto Err_LoadDb

    Set db = DBEngine.Workspaces(0).OpenDatabase( _
            "C:\MyApps\Access\DBConnect\Company.mdb", False, True)
    
    If Not Err Then 
    	MsgBox "Database opened successfully..."
	query = "SELECT * from tblCompany where City = 'London'"
	Set recSet = db.OpenRecordset(query, dbOpenForwardOnly, , dbReadOnly)
    
    	Do While Not recSet.EOF
        	Debug.Print recSet.Fields("Company Name")
        	recSet.MoveNext
	Loop
	recSet.Close
    	db.Close
    End If

 Exit_LoadDb:
    Exit Sub

 Err_Loaddb:
    Select Case Err.Number
       Case 3024 ' Database not found
          MsgBox "Database not found"
          Resume Exit_LoadDb
       Case Else
          MsgBox Err.Number & " " & Err.Description
          Resume Next
    End Select

End Sub

John

 

[inahs2k]

Hi John,

Thanks a lot again. I will be back.......

Good day,
Shahnaz.