base primary key integer auto value

[lesjen]

Hi

I’ve successfully moved my data from an Access database to LO Base. I used a row “client” to automatically apply a new unique number to a new entry. Now I have this row in my table but, I’m not entirely clear on how I can continue this numbering from the current value that I have today, I think it’s 478 at the moment.

This is important to me because I already had “relations” to two other tables where I used the “Client” as identifier so that the values these other tables hold gets pulled in correctly to a form I have.

I’ll appreciate any help or suggestion you might have. Thank You

@Ratslinger
OK! I moved my data into LO Base never the less. I’m trying to get out of the Windows world. I’m on a MacOS 10.15.5 and running LO 6.4.4.2

So how would you recommend I go about transferring the data from Access? I’ve Googled extensively and I’ve used the information I found to move the data.

Just for your information: This is the procedure I used.

1. Copy/paste Access tables to an XLS (with one tab per table) and then open the XLS in OpenOffice.

2. Create an empty OpenOffice base

3. Drag each tab into the Table panel. The advantage is that base retains the order of the field names. Each field must be defined, and there were errors, but as the XLS was open it was easier to find where the problem lay and correct it in the XLS (or in the database definition).

4. Tables were the only thing that could be imported.

[Ratslinger]

@lesjen,

LO Base is not a database. So what database did you use to move the Access data into? Would think you have an auto increment field and starting point can then be set. See → After updating I get error message when saving in Base.

Should note this as well as OS & specific LO version when posting. These items quite often can help determine a direction to take in answering the question.

[Ratslinger]

Hello,

Yes you used Base but that is not the database. Base is simply a front end to a database. When you create a Base file you chose the database to use. LO provides two embedded databases - HSQLDB and Firebird. In your version of LO only HSQLDB is apparent. Beside these choices there are many other connections available to others such as MySQL, PostgreSQL, SQLite3, and others. Which database you are using is important to note as SQL can vary from one DB to another.

If you have chosen HSQLDB embedded (can see on main screen on bottom line center), you may want to consider a different DB. This is an old DB and can in some cases lose data. There is the possibility of changing this to a split database (data external to the .odb). See → Create a new ‘split’ HSQL 2.x database.

Basing the rest of this on you having chosen HSQLDB embedded as the database.

The method you have used to transfer data is one most take when moving from Access. You could have pre-defined the tables and copied the data. By doing that you could have set the primary key as an auto increment field.

It is probable your primary key is not set. Just edit the table and select that field and set the property of AutoValue to Yes:

If you need to reset the starting value see the link in my comment under the original question.

[lesjen]

Hi
First, sorry for the faulty posting, it won’t happen again
I never responded to your explanation. You’re right I’m using the HSQLDB.

What would you recommend that I do if I want a DB that can be trusted. I have about 600 entries at the moment and normal growth is max. 50 per year.

I just need a stable place to keep my data and my ambition was to leave the MS world.

I do of course have the opportunity to run my Access DB via Parrallells on Mac but that’s not a change for me. I’m aware that LO Base is not the same but I thought I could use it?

/Leslie

[Ratslinger]

@lesjen,

What would you recommend that I do if I want a DB that can be trusted.

This is part your responsibility - backups and choosing a database based upon your needs. HSQLDB is a good database (there are many) but the embedded version provided with LO can be problematic. It is good for quick testing and I use it often for demo posts on this site. My answer provides a link which allows the HSQLDB data to reside outside of the Base file and no server need be installed. Additionally is allows an updated version of the DB and can upgrade as new versions are released.

With your comments, this is the direction which seems appropriate for you at this time.

[lesjen]

Thanks. I’ll look into “splitting” the DB.