The following code allows the easy read and write of data to any ADO supported database, you just need to use the correct connection string.

The included example can create a PC database, create a table, insert data and list it.

Requires: luacom

Code

Based on module from amsplugins (site closed in 2014).

--[[
@Title: Work with Database Tables
@Author: Jane Taubman
@Version: 1.0
@LastUpdated: 31 December 2011  
@Description: 
Example code showing the reading and writing of a MDB (Access Database) from with in Lua.
]]
 
------------------------------------------------------- Start of ADO Module
do 
local dblua_data = {}
 
require("luacom")
 
function DBCreate(file)
  local Catalog = luacom.CreateObject("ADOX.Catalog")
  Catalog:Create(connectionstr)
end
 
function DBOpen(connection_string)
  dblua_data.connection = luacom.CreateObject("ADODB.Connection")
  assert(dblua_data.connection)
  dblua_data.connection.ConnectionString = connection_string
  dblua_data.connection:Open()
end
 
function DBClose()
  dblua_data.connection:Close()
  dblua_data.connection = nil
  dblua_data.recordset = nil
end
 
function DBExec(statement)
  if statement == "%BEGIN" then
    dblua_data.connection:BeginTrans()
    return
  elseif statement == "%COMMIT" then
    dblua_data.connection:CommitTrans()
    return
  elseif statement == "%ROLLBACK" then
    dblua_data.connection:RollbackTrans()
    return
  end
 
  if dblua_data.recordset == nil then
    dblua_data.recordset = luacom.CreateObject("ADODB.RecordSet")
  elseif dblua_data.recordset.State ~= 0 then
    dblua_data.recordset:Close()
  end
  dblua_data.recordset:Open(statement, dblua_data.connection)
end
 
 
function DBRow()
  if dblua_data.recordset == nil then
    return nil
  elseif dblua_data.recordset.ActiveConnection == nil then
    return nil
  end
 
  if dblua_data.recordset.EOF == true then
    return nil
  end
 
  local row = {}
  local fields = dblua_data.recordset.Fields
  local i = 0
 
  while i < fields.Count do
 
    local field = fields:Item(i)
    row[i] = field.Value
    row[field.Name] = field.Value
 
    i = i + 1
  end
 
  dblua_data.recordset:MoveNext()
 
  return row
 
end
 
end
------------------------------------------------------- End of ADO Module
 
------------------------------------------------------- Check File Existance
function file_exists(name)
   local f=io.open(name,"r")
   if f~=nil then io.close(f) return true else return false end
end
------------------------------------------------------- Main Program Code
dbfile = "d:\\temp\\test3.mdb"
connectionstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="..dbfile
if not(file_exists(dbfile)) then
   DBCreate(connectionstr)
end
DBOpen(connectionstr)
 
DBExec("create table test (name char(30), phone char(20))")
 
DBExec("insert into test values ('Bill Gates', '666-6666')")
DBExec("insert into test values ('Paul Allen', '606-0606')")
DBExec("insert into test values ('George Bush', '123-4567')")
 
DBExec("select * from test where name <> 'Bill Gates'")
 
 
t = DBRow()
 
while t ~= nil do
  print(tostring(t.name).."\t"..tostring(t.phone))
  t = DBRow()
end
 
DBExec("drop table test")
DBClose()
 
collectgarbage()