(SOLVED) How to append/write data to an Access database

[linuxfan]

This is kind of a stupid question, but the solution is found faster here than reading the f*** documentation. Apologize for this, but I have no clue at the moment. I worked in the past with delphi and paradox, but now the system seems quite different.

So: I have an .MDB file (access database) and ODBC framework on winslow.

I dropped on a form: an ODBCConnection, an SQLQuery, a DataSource and a DBGrid (update: also a SQLTransaction). "Connected" them all, and voilà I can browse the (single) table I am interested in.

Now, my aim is to add a record to that table, and set some field in it. But I don't know how to do that. Should I operate (use methods of) the DataSource? Or should I use the SQLQuery? SQLQuery has some "sql" properties for reading, updating and so on, but I don't understand what they are used for; well, I've set the property for reading to something like "select * from ...", and it works, but appending/modifying records is another thing I suppose.

Could someone give me a simple example?

[TRon]

Now, my aim is to add a record to that table, and set some field in it. But I don't know how to do that. Should I operate (use methods of) the DataSource? Or should I use the SQLQuery? SQLQuery has some "sql" properties for reading, updating and so on, but I don't understand what they are used for; well, I've set the property for reading to something like "select * from ...", and it works, but appending/modifying records is another thing I suppose.

Could someone give me a simple example?

See "How to execute a query using TSQLQuery?"

[linuxfan]

TY TRon,

this is a good hint: "use methods of the TSQLQuery". I am on another computer now, I will try next days.

[wp]

You can also use the TDataset methods Edit, Insert, Append, Post, Cancel, or the data-aware controls. See attachment for an example how to create an mdb database, populate it by means of SQL INSERT statements, and to edit/insert record by means of data-aware controls.

But note that the example runs only as 32-bit application, I never managed to use Access databases in a 64-bit application (maybe my Office is 32-bit only?).
The (modified) example runs now as both 32- and 64-bit application; for 64-bit I had to install the accessdatabaseengine_X64 from the MS site.

[Zvoni]

Go with raw SQL-Statements.
I wouldn't trust the InsertSQL et al Properties of TSQLQuery with a long poker.

Curious: Why in blazes are you using mdb? You're limiting yourself regarding SQL-Statements, since Access misses some essential Functions in SQL (REPLACE being the prominent one)

Sidenote: I noticed that you omitted a TSQLTransaction??
I was under the impression, that the "holy" Trinity must be used?!

[linuxfan]

@wp: your answer was perfect and the example worked very well. Thank you very much, to you and all the others.

@Zvoni:
> Why in blazes are you using mdb? You're limiting yourself ...

Eh eh, because I'm forced to...
About me, I would avoid anything having "MS..." or "MICROxxxx" in its name...

> Sidenote: I noticed that you omitted a TSQLTransaction??
No, I omitted to cite it here (my fault).

What I don't understand now is: before trying the @wp example, on ODBConnection I was setting a named ODBC source via DataBaseName (IIRC). Now instead I set the driver to "Microslow Access ..." and add a Params "DBQ=filename.mdb".

Apart from the fact that this way is much more confortable (I don't have to go to control panel to add named sources): am I using a different driver now?

[Zvoni]

@Zvoni:
> Why in blazes are you using mdb? You're limiting yourself ...

Eh eh, because I'm forced to...
About me, I would avoid anything having "MS..." or "MICROxxxx" in its name...

My condolences

What I don't understand now is: before trying the @wp example, on ODBConnection I was setting a named ODBC source via DataBaseName (IIRC). Now instead I set the driver to "Microslow Access ..." and add a Params "DBQ=filename.mdb".

Apart from the fact that this way is much more confortable (I don't have to go to control panel to add named sources): am I using a different driver now?

You probably mean DSN, since with a "named" DSN you configure Driver, Database/File-Name etc. there, and in the Connectionstring you would just refer to the DSN.

That said: No, it's the same Driver

[linuxfan]

> You probably mean DSN ... No, it's the same Driver

Yes, I meant DSN (could'nt recall the name at the moment of posting).

While we are at it (eh eh): I also have an SdfDataSet, from where I should extract certain records and, if they don't exist in the MDB table, add them there. So, look in the SDF for records having some criteria, determine if they should be appended or not in MDB (are they missing in MDB?): if yes, append.

The last part (appending record) is the easy one, I think I have understood.

Browsing the SDF should be easy with TDataSet methods (First, Next, FielddByName etc).

Checking whether a record (with a unique ID) exists in the MDB raises some doubt. Should I use an SQL query and count the number of records returned (just 1 or 0)? Or is there another method?

Again, thanks to all.

[Zvoni]

> You probably mean DSN ... No, it's the same Driver

Yes, I meant DSN (could'nt recall the name at the moment of posting).

While we are at it (eh eh): I also have an SdfDataSet, from where I should extract certain records and, if they don't exist in the MDB table, add them there. So, look in the SDF for records having some criteria, determine if they should be appended or not in MDB (are they missing in MDB?): if yes, append.

The last part (appending record) is the easy one, I think I have understood.

Browsing the SDF should be easy with TDataSet methods (First, Next, FielddByName etc).

Checking whether a record (with a unique ID) exists in the MDB raises some doubt. Should I use an SQL query and count the number of records returned (just 1 or 0)? Or is there another method?

Again, thanks to all.

Since MS Access doesn't support INSERT IGNORE (like MySQL) (Have a guess what INSERT IGNORE does....) your only way is to check beforehand
As for checking if a Record exists in the DB: I'd turn it around: Check which one DOESN'T exist, and INSERT directly.
Should work with a LEFT JOIN with a IS NULL in the WHERE-CLAUSE

No idea, if MS Access supports JOINs in INSERTs, either...

OTOH, you could switch off Error-Handling (try...except/finally), and just fire off the INSERT, irrespective if it exists or not.
If it exists, you'll get an Exception you can ignore, and we already had that discussion in this Forum

EDIT: Just looked it up: MS Access supports JOIN's in INSERTS, but it's a bit convoluted.
Anyway, it would do away with the need to lookup beforehand if an ID exists

EDIT2: As a "concept" how i would approach this:
1) Create a String-Array. RowCount is the Same as your SDF-Dataset, Columns as many as you want to insert into the Access-DB (Optional!!!)
2) Run through your SDF "collecting" the Values into that StringArray, in the Format "SELECT ValueOfID As AliasOfID, ValueOfCol1 As AliasOfCol1, ValueOfCol2 As AliasOfCol2....". "SELECT" is hardcoded! The Alias is only needed at the first Array-Member
Note: You have to "quotify" columns where neccessary
3) Join the String-Array with Delimiter " UNION ALL " (Note the Spaces before UNION and after ALL!!!)
This results in a String e.g.

Code: SQL  [Select][+][-]

1. SELECT 1 AS ID, 'John' AS FirstName, 'Smith' AS LastName UNION ALL SELECT 2, 'Joe', 'Miller' UNION ALL SELECT 3, 'Jane', 'Doe'

Then it would be e.g. (Aircode)

Code: SQL  [Select][+][-]

1. INSERT INTO MyAccessTable(ID, FirstName, LastName)
2. SELECT T.ID, T.FirstName, T.LastName FROM
3. (SELECT 1 AS ID, 'John' AS FirstName, 'Smith' AS LastName UNION ALL SELECT 2, 'Joe', 'Miller' UNION ALL SELECT 3, 'Jane', 'Doe') AS T
4. LEFT JOIN MyAccessTable AS M ON M.ID=T.ID
5. WHERE M.ID IS NULL

If record 2 (Joe Miller) already exists in your MDB, it gets filtered out

Upside with this Approach: You can test everything after the INSERT, if it returns the correct Records before you go live in production with this.

[linuxfan]

Thank you again for your interest, Zvuoni.

I think I have not explained well. The problem to solve is this one: I have an Access table (call it "MDB") containing electronic parts to be put onto electronics boards. The table contains many PartNumber's and other technical data.

Then I have another table, in a TFixedFormatDataSet, obtained by an export from the general factory stock database. This table contains the electronic parts, and other items not suitable to be put onto an electronics board, like stationery, furniture, chemical products and so on.

When a new (or a few) electronic component has to be deployed, first a new part number is put in the general stock database; then an export is done in order update the MDB. This temporary file can be called "FDS".

I want to do the following:

  1) Scan the FDS database to see what items are electronic components. No chairs, tanks of solvent, books or 19" monitors have to be considered.

  2) For every item found, check if it is already in the MDB table. If it is already there, skip it. Most of the items are already in the MDB table.

  3) A StringGrid is presented to the user. The grid contains the (few) items recently added, with their description and category. There are also blank cells so the user can fill the missing technical data.

  4) When the user is satisfied, he/she confirms and the records from the StringGrid are added to the MDB table.

I suppose I can not use a single SQL statement to compare the two tables (MDB and FDS), because the tables reside in two totally unrelated databases. But, I want to collect all the necessary data beforehand, to let the user check all the items he needs are loaded in the factory stock. The user fills the missing data, and finally updates the MDB table with the (few) items.

Crucial is to check whether a Part_Number in FDS is alreay present in MDB. Depending on the outcome, I can first for the presence in MDB, or first check for the item being an electronic component. Either ways, I suppose it will be fast, because the MDB contains about 1500 items and the FDS about 7500.

Sorry for not having explained clearly the first time, I hope it's clearer now. Your suggestion is awesome anyway, but I probably will use several "INSERT INTO MDB (field,field...) VALUES (...)" (or something like that).

About checking if a given part_number is present in a table, I don't see any other way than use an SQL like "select Part_Number from MDB WHERE Part_Number='xxx'". Or I am wrong?

Lastly, I must say this forum is useful, fast and warm. Great! Thank you all again.

[Zvoni]

Hmm…… if i have records from two completely unrelated databases, i‘d use a SQLite InMemory database, throw both datasets in there, do my select, and i get what i need.

No need to check each single record if it exists