Base - need to re-point a form

users
#1

I have a base file attached to a MySQL database on a remote server. I have
a form that I use to view and edit the data in one table.

Others also access the database, and I need to experiment with the data in
that table without molesting the master copy.

One solution would be to make a complete copy of the database, but as I
want to make copies of the main table many times a day that would be a
laborious solution

It is easy to make a duplicate of the main table within the database, and
also easy to make a copy of the form in the base file, but what I need to
do now is point the copy of the form at the copy of the table.

I thought it might be possible to open the odb file as a zip file and find
the form data containing the table name somewhere in it, and edit it to the
copy table name, but I haven't succeeded yet.

Does anybody know if this is possible - or even if there is a much easier
way?

#2

John Clegg wrote:

I have a base file attached to a MySQL database on a remote server. I have
a form that I use to view and edit the data in one table.

Others also access the database, and I need to experiment with the data in
that table without molesting the master copy.

One solution would be to make a complete copy of the database, but as I
want to make copies of the main table many times a day that would be a
laborious solution

It is easy to make a duplicate of the main table within the database, and
also easy to make a copy of the form in the base file, but what I need to
do now is point the copy of the form at the copy of the table.

I thought it might be possible to open the odb file as a zip file and find
the form data containing the table name somewhere in it, and edit it to the
copy table name, but I haven't succeeded yet.

Does anybody know if this is possible - or even if there is a much easier
way?

John,
I am assuming you need to change the table that your form points to. You can do this by:

   1. Bring up your form in edit mode (right click on form name and
      select "Edit".)
   2. Select the "Form Properties" window (this is the icon that looks
      like a window with 2 buttons with an arrow pointing to it, and
      with a help balloon that says "Form".
   3. In the popup window, select the "Data" tab.
   4. Change the "Content" box to the table you want to use from the list.

If the new table's column (control) names are different, you will have to change those too in the form editor. You do that by Ctrl-left-clicking on the form's control box and selecting its "Data" tab and selecting the new name from the list derived from your new table. Do that for each control that has a different name from your old table.
Hope this helps.
Girvin Herr

#3

That's exactly what I wanted and couldn't find anywhere! I'm very grateful

John

#4

OK, a supplementary if I may!

I am accessing the remote MySQL database using com.mysql.jdbc.Driver.
When I access the data by opening the table or using the form it is
read-only. Any ideas again?

John

#5

Hi John,

I am accessing the remote MySQL database using com.mysql.jdbc.Driver.
When I access the data by opening the table or using the form it is
read-only. Any ideas again?

Several possible reasons :

- no primary key field defined in your table, but I guess you would have
noticed that before, with your previous attempts to edit the data from
within LO Base ;

- a field in your table that LO can not parse - unfortunately, some data
types, whilst in theory recognised by the driver in other circumstances,
i.e. with other software, are not recognised by LO's data type parser,
for the lack of them being defined in the handling code. One such
example are certain binary objects. At present CLOB support is limited
to images in LO ;

- a corrupt or misconfigured ODB file : it may be that changing your
table source has confused LO's database registration code and caused it
mark the table as read only, or alternatively, if it points to a
separated database, then the ODB configuration needs to be reset to
correspond to the new database in addition to the new table (this is
just speculation on my part) ;

- a bug in the version of LO you are using.

Alex

#6

That's pretty comprehensive, thanks! I will work through those.

Regards

john

#7

Is this after you've switched to using a different copy?
It sounds like you have had read/write access in the past, is that
right? If so, what changed?

How do you know it's read only?
If your form won't let you change the data it's a local issue, if you
can save but it makes no difference presumably thems who are
administering the MySQL have set something.

Often the reason for having read only access through Base is that the
table in question doesn't have a primary key defined, but if you've
had write access before and nothing else has changed that would be
unlikely.

Regards
Mark Stanton

#8

Thanks. This is a fairly new connection, so I can't be sure it has ever had
read/write. I know it's read-only because if I open the table and click
Edit everything is greyed out except Copy. And if I access data via the
form I can't change anything.

I can access the data via phpMyAdmin and make changes. I have added a
primary key this way, but I'm not sure that Base is seeing that - there is
no sign of it if I edit the table structure in Base.

#9

Hi John,

I can access the data via phpMyAdmin and make changes. I have added a
primary key this way, but I'm not sure that Base is seeing that - there is
no sign of it if I edit the table structure in Base.

How is your primary key defined ? - single or multiple field ?

Additionally, you might have to quit LO completely, and then reload the
ODB file.

Alex

#10

It's a single field, integer and unique. And having been out and in again
base can see the index now, but I still can't edit.

#11

Dumb question perhaps, but : user privileges ? Any difference between
Base user and phpmyadmin user ?

Alex

#12

Can you post the table structure ?

Alex

#13

No, same user!

#14

The structure is very simple - one integer field, indexed unique and 33
fields text(varchar) of varying lengths all unindexed.

#15

Update - I have now tried connecting with ODBC (MySQL 3.5.1, Win7) rather
than JDBC. The table now becomes editable, but when I make a change I can't
save because I get a message that nothing has changed!

Am I jinxed??

#16

Hi John,

Update - I have now tried connecting with ODBC (MySQL 3.5.1, Win7) rather
than JDBC. The table now becomes editable, but when I make a change I can't
save because I get a message that nothing has changed!

Shot in the dark - the ODB file isn't on a SMB/CIFS or NFS server or
network storage ?

Unfortunately, I can't reproduce your problem from a general point of
view - yesterday I managed to set up a JDBC ConnectorJ connection to a
locally hosted mysql db on WIndows XP and LO 3.6.1 and can edit the data
and have it saved no problem.

So, it must be something specific to your setup, or the ODB file itself.

Alex

#17

No, the ODB is on a local NTFS volume

The problem with the ODBC version is that Base can't download the table
structure from the server for some reason. The JDBC version can but can't
r/w. Which all suggests to me some dodgy MySQL connectors

To check that I set up another MySQL server elsewhere and had exactly the
same problem with the JDBC connector.

I suppose I could set up a local MySQL server but I don't want it local!
And I suspect I might hit the same problem anyway.....

Very frustrating!

#18

Hi John,

The problem with the ODBC version is that Base can't download the
table structure from the server for some reason. The JDBC version can
but can't r/w. Which all suggests to me some dodgy MySQL connectors

There are some known issues with ODBC connectors, some of which cause
crashes, or data access problems, depending on which version of LO you
are using and which version of the MySQL ODBC Connector. Remember, LO is
32bit, even on Windows 64bit OS, so it requires a 32bit ODBC connector.

This is also a major problem on OSX, where :
- current versions of the OS are 64bit only as are the included mysql
servers ;
- LO is only 32bit ;
- OSX can't manage a 32bit and 64bit mysql connector at the same time
(or at least if it can, I've found no way to get it to behave properly),
so users are stuffed when it comes to MyODBC on Mac OSX unless they are
using a 32bit OS, i.e. Leopard or older..., or Snow Leopard in 32bit
kernel mode...

On Linux, the ODBC connector and LO are available in 32 and 64 bit
incarnations, so it is possible to get something up and running.

All versions of LO have had problems accessing remote database instances
via ODBC, especially if your db server is on the internet.

To check that I set up another MySQL server elsewhere and had exactly
the same problem with the JDBC connector.

I suppose I could set up a local MySQL server but I don't want it
local! And I suspect I might hit the same problem anyway.....

No, I suspect that your problem is precisely because your mysql server
is not local, or at least, not on the local lan (if I have understood
your implied setup correctly).

I don't have any problem accessing a LAN hosted mysql server instance
over the mysql JDBC connector. Trying that over the internet is a whole
other ballgame though. You need to have remote user access to your mysql
server instance other than through port 80, which is not always (rarely,
other than by SSH) provided for by hosting services. On top of that, DNS
resolution issues can cause timeouts.

It might be helpful if you gave us the exact setup you are trying to put
into place, with version numbers for the different components.

Alex

#19

OK, thanks for that.

Using LO 3.6.2 on Windows 7SP1 (also linux, but let's get it working here
first!). Connecting to a database on xeround.com (actually hosted on Amazon
EC2 servers, using port 18993. JDBC driver class com.mysql.jdbc.Driver
using mysql-connector-java-5.1.22-bin.jar

John

#20

Hi John,

OK, thanks for that.

Using LO 3.6.2 on Windows 7SP1 (also linux, but let's get it working
here first!). Connecting to a database on xeround.com
<http://xeround.com> (actually hosted on Amazon EC2 servers, using
port 18993. JDBC driver class com.mysql.jdbc.Driver
using mysql-connector-java-5.1.22-bin.jar

Can you connect to that instance via the mysql client program from the
command line interface and edit data in your tables on the remote instance ?

mysql -h myhost -P myport -d mydb -u myuser -p mypassword

Alex