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

• get Access2SQL   access2sql.zip a 32-bit windows-command-line-application
  
• Extract the Access2SQL.exe (1 file) place it whereever (preferable in path) and run (from command line)...

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)

Key	Default	Description	Example
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');

Windows 10 / ODBC 32 note

• In modern Windows 10 the ODBC 32bit Microsoft Access Driver (*.mdb, *.accdb) driver is not installed by default (you need this to read the .accdb format)
• Fetch the driver from microsoft.com/en-us/download/details.aspx?id=13255
• It is the 32bit AccessDatabaseEngine.exe you want (not AccessDatabaseEngine_x64.exe)

Versions / History

2018-jan-09, version 0.8

more documentation

2017-nov-17, version 0.5

first-web-available release.

2015, version 0.1

implemented

More info

More info/questions from:

Subject: Access2SQL