Access2SQL: Simple Command Line MS-Access-DB-to-SQL-text-file tool
HOWTO get sql-text from an access database using windows command line

This simple tool

Use a command-line-tool to script update an sql database from an access db.

Access2SQL.exe


Download / Installation

Example

access2sql.exe -c=convert.ini
Use the config file convert.ini for options

Config params/options

Options for access2sql can be passed by command line or set in a config file or both.
From command line use -key=value or --key=value or /KEY=value format
(-? | --help | -c|-config options from command line only)
KeyDefaultDescriptionExample
i | input | file | mdb MS-Access-DB to connect to this is the input database File=c:\temp\db.mdb
o | output Standard Output Where to output the sql-text Output=c:\temp\db.sql
d | driver Microsoft Access Driver (*.mdb, *.accdb) Name or the ODBC32 'driver' to use Driver=Microsoft Access Driver (*.mdb)
u | user use this username when connecting to access-db User=admin
p | password use this password when connecting to access-db Password=secret
ts | timestamp (today) can be used with %timestamp in sql -ts=%_date (usually from command line)
ty | typ | type TAB/HEAD type of output, se below for options Type=INSERT IGNORE
s | sql sql to ask from the access-db Sql=select * from table where created>=#%timestamp#
t | table table to use in sql-text-output Table=table
f | field| fields * fields to export to sql-text-output Fields=col1,col3,col5... or Fields=1,2,4-8...
k | key | keys - (no keys) field list, keys to skip in
ON DUPLICATE KEY UPDATE format
Keys=id
bools | booleans - (no fields) field list, convert True/False to 1/0 Keys=id
null | empty-null - (no fields) field list, convert empty to null Null=*
m | mod 1000 new insert command for every row... Mod=100

Type option

TAB
convert to text-file tab delimited by tab
TAB/HEAD
first row include field names
INSERT
produce sql-text-file: INSERT INTO table (a,b,c..) VALUES (x,y,z..),(x,y,z..)..;
INSERT IGNORE
produce sql-text-file: INSERT IGNORE INTO table (a,b,c..) VALUES (x,y,z..),(x,y,z..)..;
INSERT DUPLICATE
produce sql-text-file: INSERT INTO table (a,b,c..) VALUES (x,y,z..),(x,y,z..).. ON DUPLICATE KEY UPDATE a=values(a)..;
APPEND
append just some sql text

Exampel config-file

The config-file (windows-ini-file-format) [sections] is read in order.
The section name should be unique, but is not used.
The first section holds common options and must contain Driver and File option.
##
# File: Access2SQL.ini
#
[Config]
Driver=Microsoft Access Driver (*.mdb)
File=C:\Temp\db.mdb
Output=C:\Temp\db.sql

[Use]
Type=APPEND
SQL=USE sqldbname;

[Table1]
Type=INSERT DUPLICATE
Sql=SELECT * FROM accesstable1 WHERE created>=#%timestamp#;
Table=sqltable1
Keys=id,nr
Bools=water
Null=*

[Table2]
Type=INSERT DUPLICATE
Sql=SELECT * FROM accesstable2;
Table=sqltable2
Keys=id
Null=*

[Call]
Type=APPEND
SQL=CALL Procedure(%projid, '%timestamp');

Versions / History

2017-nov-17, version 0.5
First-web-available release.
2015, version 0.1
Implemented

More info

More info/questions from:

Subject: Access2SQL

Keywords: reinhardt, accesstosql, access2mysql, accesstomysql, access2mariadb, accesstomariadb, access, microsoft, msaccess, mdb, sql, mariadb, mysql, command, line, cmdline, windows, tool, utility