Read/only problem with linked MySQL tables

Hello!

I'm using Access 2003 and MyODBC 5.1 driver to connect to a remote MySQL server.

I have to connect from code, because I have timeout issues (which can not be resolved from MyODBC 5.1 driver interface; no more "Initial statement" option).

Anyway after a painful process of getting this to work from code I have a problem that I seem to be opening the tables in read-only mode.

Where is the problem? I want to have read/write acces.

Here's the code:


	Code:
	    Dim wsODBC As Workspace
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim conODBC As Connection
    Dim qdef As QueryDef
    
    Dim baza As String
    Dim user As String
    Dim pass As String
    Dim connect As String
    Dim dsn As String
    
    baza = "baza"
    user = "user"
    pass = "pass"
    dsn = "PD"
    
    connect = "ODBC;" & "DATABASE=" & baza & ";DSN=" & dsn & ";UID=" & user & ";
                  _PWD=" & pass & ";STMT=180"
               
    Set wsODBC = CreateWorkspace("NewWorkspace", "admin", "", dbUseODBC)
    Set db = CurrentDb
       
    ' še s povezavo
    Set conODBC = wsODBC.OpenConnection("con1", dbDriverNoPrompt, False, connect)
       
    Set rs = conODBC.OpenRecordset("SELECT * FROM cscart_users;", dbOpenDynamic)
    
    With rs
        .MoveFirst
        Do Until rs.EOF
            If rs![user_id] = 23 Then
                .Edit
                ![firstname] = "Aljosa"
                .Update
            End If
            .MoveNext
        Loop
    End With
    
    rs.Close
    Set rs = Nothing
    qdef.Close
    Set qdef = Nothing
    db.Close
    Set db = Nothing
    conODBC.Close
    Set conODBC = Nothing
    wsODBC.Close
    Set wsODBC = Nothing

I'm getting Run-time ERROR 3027 - "Cannot update. Database or object is read-only."

Thanks in advance!

Miha


Post your answer or comment

comments powered by Disqus
I am having a problem with linking two tables in order to make a relational database. My problem is that whenever I change an information in one table, it does not seem to change in the other.

Any solutions?

Does anyone know why when i have a link excel spreadsheet in my access database i am not able to edit the data?
I have a copy of the database on my laptop and it works fine, i can edit and add data but the office copy which is on the network it will not let me edit the linked spreadsheet in access. I have an update query that updates a field in the linked excel table but it no longer works. I dont think there is any special permissions on the files. It is sooo frustrating... dont know what to do.

Thanks for letting me vent.

Hi.

I have a problem with linked tables. I have a linked table and I want to add some fields to the data. I know I can't add new fields to a linked table ( because it's read only ), so I made a new table in my project. In that table I added a few new fields that I need for my application. So far I made relationship with linked table and my new table with key field, which is product code. So my question is how can I automaticly update the table with linked table and leave added field intact?

Regards, Brixie.

Hello. I am trying to link a MySQL database to my Access file and am having some trouble. First off the MySQL database was created by my other, industry specific, software provider. They recently updated to the MySQL format so people like me could now access the data outside of that software. They also had a stipulation of "you're on your own" if you run into any errors or problems when accessing/manipulating data.

So I was able to set up and link the tables in the database to my access file, but one of 2 problems occur depending on the settings.

1. The primary key's data type of most of the tables in the MySQL DB are set to BIGINT. When I link the tables, Access reads this as a text type. Then I get data type mismatch errors when trying to run queries, inserting subforms and etc, because the related fields in other tables are of regular INT data types and access is reading it a number format.

2. After research on this BIGINT problem I found a solution, but it creates another problem... The solution was to Select the Change BIGINT columns to INT option in the connection dialog in ODBC DSN Administrator and creating a dummy column with type Timestamp. After doing this and relinking the tables, the data type is now correctly showing as number, but whenever I try to view data in the table (either directly from the table, in a query, or on a form based on the table/query) every field in every record is marked as #DELETED#. The total number of records in the table is correct however. Most of the tables already had a field/column of Timestamp type, but even if I added another one I still got the #DELETED# problem. Oddly enough though whenever I added the new field to the table that field did not show up in the table after re-linking.

Any suggestions on fixing these problems?

I have a problem with creating a link between Excel spreadsheet and a table in Access. When I finish linking it, Some of the field is listed as "#Num!". I tried changing the cell format from "number" to "general" to "text" in Excel, but nothing seems to work.
Anybody know what's wrong with it?

Thank you

Hi i posted about spliting a database, which i did, it was to alllow more than one user to use the database at the same time. I have 1 machine (#1) with the tables only, and on the same machine a front end for the data with linked tables.

On the other machine (#2) i have a front end with linked tables to the tables only database on machine #1.

The front end on machine #1 works fine. On machine #2 the front end can see the data, but the data is read only ? I have put the tables only program on machine #1 in the public directoy and shared it, but to the front end on machine 2 it is still a read only recordset().

Can anyone give advice on this ?

I have a database with front end and a back end databases. I want to share this database with other users on CD.

The problem is that when copied to a local hard drive from the CD, the read only properties of the databases are set.

Rather than rely on the user having the knowledge to right click and change it back manually, can i do this with VB code automatically for them on opening it?

I have a database on a network which has no security setup; everyone who has rights to its directory has full rights to the application. From time to time, it becomes "Read Only" and no one can do anything that requires writing to the local tables (the back end database is "Read Only" for everyone.) Checking the database properties shows the database as not being "Read Only" and changing it to read only and back again does nothing.

So far, the only way I have been able to solve this problem is to replace the "Read Only" database with a backup copy that is not "Read Only".

What could be happening here?

Hi,

I am having trouble doing a sort in a query (and report too)

I have a field called SP ITEM which looks up data in another table to get more information related to the SP ITEM.

Whenever I sort this SP ITEM it doesn't actually sort by the SP ITEM it sorts by the ID field from the other table. How do i make it sort by the SPITEM not the ID?

I also have the exact same problem with the sorting and grouping in the reports.

I've attached a screenshot (zipped in a .doc) of the query to hopefully make what i'm saying clearer...

Thanks!
simon

I have created a Form and specified it as read-only. The problem is that I want with a press of a button to open as read-write. I can^t figure how to do this. Can you help me pls.
Also I want when a change is done to the form to be prompted to accept or reject the changes on the exit of the form.
Help me pls.

I'm having a very strange problem with my Access application. I'm using an Acces frontend application and my data is placed in a MS-SQL 2000 backend database.Because of slow communication between two offices, I have merge replicated my MS-SQL database. I make two version of the frontend, one linked two MS-SQL server 1 and the other to MS-SQL server 2.

When I link the tables in the MS-SQL subscriber database I'm loosing the key in many of my tables - some of the tables don't have the problem. I can't see any different in the tables where it keep the key and in the tables where it doesn't. When I look at the tables from within SQL Server Enterprice Manager the key is there, but when I look from within Acces (where it is linked to) it is not. This is resulting in that I cant add records to the tables with missing keys.

I don't see the problem when linking to tables in the MS-SQL publicher database.

Hope somebody can give me an answer on this.

Romanov

Hi I wonder if someone could help - I've tried to find the cause of my problem with no success?

I have a macro set up to import an Excel Spreadsheet - infact I have several macro's set up to import several spreadsheets. However my users are only reporting a problem with one - The table imports fine, however there are about fifteen blank records at the begining of the table - all data is complete and I have to go in each day and delete these blank records! I don't know why it's doing it - I have checked all my data types against the spreadsheet and they are fine. Checked the data in the spreadsheet - no blank rows!
This never used to be a problem and I just don't know what it is! I can live with deleting the records but I would like to get it sorted if possible!

Thanks

I have a backend db that gives me the error "the database 'dbname' is read-only. You won't be able to save changes made to data or object definitions in this database."
The db is not read-only and this only happens with a particular user who is set as a "power user" with XP Pro. If I change the user to "Administrator" the error disappears and all is well. However, I don't want the user to have Administrator privileges.

Any thoughts?

I am developing a database which will track some of my companies clients bonus trips. Basically everyone is going to the same place but folks are of different levels. Level A is a 14 day trip with X number of activities, Level B is a 10 day trip with Y number of activities and so on. The major things I'll have to track are all of their personal information as well as 'Air Travel', 'Hotel Stay', and 'Recreational Activities' information.

I can put all of this information in to one table (tblTraveler), or am I better off with creating tblTraveler holding only personal information and then linking to other tables such as tblAir, tblHotel, and so on? I'm not entirely new to Access, but no pro yet for sure. Only problem with creating one table is that it would have one whole lotta fields. Not sure if it's better developing technique to split everything up.

Also, the only thing I can come up with as a primary key is the people's last names. The client numbers are the same on many of them, so that's the only thing I can come up with. The PK really is only a factor if it's best to create multiple tables for the traveler. At first I thought it was best to split everything, but now I'm seeing less reason to take all of the travelers information and dump in seperate tables since most of the time on the master reports the trip planners want everything lumped together. Perhaps I'm better off just keeping the forms clean and seperate and just one table? The reports will do the rest for me. Any input greatly appreciated.

Hello....

Here is what I am doing. It is an inventory database that also is an order tracking per se database.

When you are entering in the order, the top part is the vendor with an order number, date etc (will show table later).

The subform is the order details. This is the tricky part that I am having problems with.

In the subform I also need to have the unit that is placing the order for that particular item.

IE
product 1 pens black ball point unit=exams
product 2 pens red ball point unit=admin
product 3 paper legal color white unit=personnel


I can get it to work but the unit part is what is driving me nuts.

I am attaching a very stripped down version no queries etc....

Thanks

Guys, need help for Multiple value in form. i have database with link backend table in the SQL server 2005. i would like to add as multiple value like in the Access table. but i cant do it when in link table in SQL 2005. thanks to help me... i am so appreciated.

Hello,
I have created a database to track onhand inventory. I am having a problem with appending data from one table to another. When I try to create the append query the destination table does not appear in the dropdown list of tables to append to.
The destination table GROUPSLOOKUP contains three fields, with the LOTNUMBER being the indexed field with 'NO DUPLICATES' turned on.
The origin table is Stocked_Items. Attached is the database. Any help you can offer would be greatly appreciated.

Thanks,
Kerry

I tried to delete all the records in a table and then import new data froma text file. I tried:

DoCmd.RunSQL "delete * from ZPAY6176"
DoCmd.TransferText acImportDelim, , "ZPAY6176", "H:AccessNew Carpoolz_pay617.lis", False


Which deleted all the records but then I got a:

"Cannot Update. Database or Object is Read Only"

Is that because the table ZPAY6176 is still being used by me? Do I need to release it somehow? If so, how? If not, how do I get around this?

I checked and the database is not read only. And I can manually add new records to test if the new ones are deleted when I click the button with the above code and they delete again too.

I have a form that as part of the OnLoad event, looks up some values. (Access 2007)

txtDEVctd.Value = RoundCurr(Nz(DSum("[DEVcost]", "Q-DEVcost", "VORef = " & VarNo.Value), 0), 2)
txtDOVctd.Value = RoundCurr(Nz(DSum("[DOVcost]", "Q-DOVcost", "VORef = " & VarNo.Value), 0), 2)

txtDEVctd.Value and txtDOVctd.Value are UNBOUND

Despite trying everyway possible to open this form in Read-Only, it stubbornly remains in Edit mode because (I have discovered through line by line testing), the txtVOctd.Value text control in the Load code is BOUND when the next line runs.

txtVOctd.Value = RoundCurr(txtDOVctd.Value + txtDEVctd.Value, 2)

Now if I make txtVOctd UNBOUND and instead use the piece of SQL shown below, the form will open Read Only.

DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE [Variation Register] SET VOCosttoDATE = " & txtVOctd.Value & " WHERE VARNo = " & VarNo.Value)
DoCmd.SetWarnings True


SO ... my only question is WHY does having txtVOctd as a BOUND control prevent the form from Opening Read only when asked to do so?

I am as ever intrigued. Many thanks

I am trying to automate my Table export process and have been getting the error message: "Cannot Update. Database or Object is Read-Only.'
None of the the Tables are read only, I'm not sure they even could be. Here is the code I am Using:
Private Sub btnExportTest_Click()
DoCmd.TransferText acExportFixed, "HazTrakExportSpecs", "tblHazTrak", "C:Documents and SettingsnancyDesktopBrianExportTest", False
End Sub

I got this code from searching this site and the Access Help.

This is the final piece to my DB. Any input would be greatly appreciated.

Brian

I have a Form that when opened runs through a Recordset, using the code below. However, it looks like the Query is uneditable because I get the following error:

cannot update. database or object is read-only 3027

Is it not possible to update records using a Recordset if your Form is based upon a Query?

Dave


	Code:
	    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryAdwordsAzoogledCleaned", dbOpenDynaset)

    rs.MoveLast
    rs.MoveFirst

    Do Until rs.EOF

        rs.Edit

        Select Case Profit

        End Select

        rs.Update
        rs.MoveNext
    Loop

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing



Help!

I've split my database and posted the back-end on the network, and rolled out the front-end to other users. It works great on my machine, but other users, when they open the front end, get a message that the database is read-only, and cannot make any alterations to the back-end (even some queries won't run).

I've done the following:

1.) Made sure that the "Read-Only" boxes are unchecked for both the front-end and the back-end.

2.) I'm not using security, but I went in and made sure that the Admins and Users had full permissions, and copied my System.MDW file to the users computers.

What is going on? Does anyone know?

Thanks!

Chris

When i place my newley created database onto a shared server. 1 of my end-users can only open the file in Read-only. The other 2 users can open it fine. The IT group at my company says there is not funding to support user created databases and wont help. Has anyone had any read-only to just 1 person issues?

Appreciate any help

i have a form, which consists of a tab control, in each page of the tab is a different subform. there is also a drop down list which allows users to select the record they want to view, i could lock the records yes, but i also want the same form to be available as a writeable from.
on my switchboard i have 2 buttons "read only form" and "Edit form"
my login system denies access to the edit form if users only have little access, so how do i make the read only form, while stil enabling the drop down, but disabling the fields for editing??

thanxs in advance


Not finding an answer? Try a Google search.