Wish: data export as SQL Insert command

[the-Arioch]

i wish the data could be put into the same SQL script as the "create Database" statement

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb208861

[joyfullservice]

The above link appears to be broken... Could you expand a bit more on what you are wanting to accomplish?

[the-Arioch]

sorry, i was just looking if Access SQL has a native multi-row insert (or bulk insert, or batch insert), like ANSI SQL and MS SQL do.

It seems, Access does not have, albeigt there is a common workaround using select/union/select

• https://www.access-programmers.co.uk/forums/threads/insert-into-with-multiple-rows.152751/
• https://stackoverflow.com/questions/6162982/insert-multiple-rows-using-one-insert-statement-in-access-2010
• https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb208861(v=office.12)?redirectedfrom=MSDN

[the-Arioch]

i just want a meethod to convert an MDB blob into GIT-able script. The CREATE TABLE parts work nice, but the data part does not

[joyfullservice]

i just want a meethod to convert an MDB blob into GIT-able script. The CREATE TABLE parts work nice, but the data part does not

Thanks, this is helpful background. When you say MDB blob, are you referring to a field with binary data like OLE attachments? Also, how did the XML format work with your particular use case? XML is typically able to produce a reliable output that works nicely in GIT, in my experience.

[the-Arioch]

Nope, I refer to the whole MDB file, which is kept as is in the program distribution. The blob-ness here is regarding source control, not database.

Internally that MDB is a subset of main database, few key tables. Used to redistribute updates.

Usually the updates only change the data (rows) or perhaps meta-data (columns) of axillary tables. And that works fine. We copy this stub file into a new update DB, populate it, and send downstream.

But occasionally the update change the definition of those key tables themselves. And then it has to be made twice, in the "schema conversion scripts" within the application sources (which are in the SCM), and in this MDB blob in the distro (which is not in the SCM).

That is fragile. So we decided do get rid of this ready-made MDB stub, and instead create it by ADOX and SQL commands, so in every given git branch that schemas of main and update databases would always be in sync.

Also, removing that opaque MDB file from distro building would eliminate potential "Frankenstein build" scenario.

So, we naturally sought for converting that Access database into SQL script to avoid tedious and error prone manual labor. I was truly surprised Access didn't have it out of the box. But your add-in checked most boxes.

Except the data itself. Which is very few in the stub database, so no big deal. We did it manually from XML. But yet, the desire for engineering perfection calls for the whole database, declarations AND rows, to be exported into SQL by One True Tool :-)

[joyfullservice]

Thank you again for the additional background. Yes, you are exactly right that Access doesn't have a good conversion to VCS friendly source files out of the box, that's precisely why this add-in was developed. The aim of this tool is to produce a comprehensive set of source files that can build an exact copy of your database at any point in the development lifecycle. I don't even keep my binary mdb/accdb files in version control any more. I just keep the source files since that is where I manage the changes, and from which I build the binaries.

The add-in does support exporting table data, and the best format for this is XML. If you export source files from a database, and have all the tables set to export their data as XML, you should be able to build this database from source and have both the structure and data fully reconstructed from the source files.

As far as automating the build pipeline, this is something that several of us have an interest in developing, but I don't know that anyone is quite at that point yet. (Personally, I am looking at the direction of a GitLab runner on a Windows machine that kicks off a build tool to build a database from source using this add-in.)

In your particular use case, I can why you were looking for automatically generated insert statements for the data. It would be handy to pass those over to SQL to load the data. In Access you might start running into issues if you try to build a giant union query. (It may throw errors when adding 50 or more rows at a time, since each row adds another union.)

One approach you could take is to tap into the AfterExport hook with your own VBA code in a standard module to export your tables as CSV or some other easily readable format. Then in your build pipeline, you could loop through the rows in your CSV file and dynamically build the SQL statements to insert the data a row at a time. (Assuming you are using another tool for the main build pipeline.)

Hope that helps!

[the-Arioch]

The add-in does support exporting table data, and the best format for this is XML. ...build this database from source

Yes, if the goal is having repetitous 2-way sync, like git post-switch and pre-commit hooks, then it makes sense, as well as tightly coupling to Access, both in using its XML dialect as foundation and saving as many Access-specific details (inevitably lost in SQL) as possible.

In your particular use case, I can why you were looking for automatically generated insert statements for the data. It would be handy to pass those over to SQL to load the data

Yep, it was a one-way one-time escape. As soon as this new code proves itself - the binary MDB file would be gonr for good.

And surely i could and would develop XML-to-SQL conversion tool by many possible means. Just, the data was too few so manual conversion was faster.

At the same time were were not interested in keeping the "visuals" of Access. Raw SQL was enough. It also would be possible, though unlikely, to eventually get rid of Jet/ICE in favour of x-platform tools like SQLite or Firebird. Frankly, Jet SQL is awful. Even lack of comments hit badly.

---

So, i do not argue from some pressing and practical usecase. I just talk from the "completeness" and "perfeciton" viewpoint. The tool that claims to export MDB to SQL would be more complete if it could do including data rows.