Source to learn how MS-Access databases are created

[LeMario (Mario Giesel)]

I find it difficult to find a source (book, article, website, etc.) that explains how to build MS Access databases with Go so that a beginner can understand it. Any ideas are welcome!

[johandalabacka (Johan Dahl)]

Hi. Have you seen this?

mattn/go-adodb/blob/master/_example/mdb.go

[LeMario (Mario Giesel)]

Thanks, Johan,

that’s at least a step forward! However, there are no explanations or comments so that I can understand what’s being done. So I’ll try to understand as much as I can from the code you pointed out. If anybody knows some additional source with explaining sentences that would be great, though.

Thanks again!
Mario

[johandalabacka (Johan Dahl)]

Hi. I havn’t tested this myself but the first part is specific for Acess and most of the rest is ordinary go sql functions

package main

import (
	"database/sql"
	"fmt"
	"os"
	"time"

	"github.com/go-ole/go-ole"
	"github.com/go-ole/go-ole/oleutil"
	_ "github.com/mattn/go-adodb"
)

var provider string
// This function just creates a file f and register it in the database catalog
// it also sets the kind of provider in the provider variable
// This would rather be returned from this function together with the error
func createMdb(f string) error {
	unk, err := oleutil.CreateObject("ADOX.Catalog")
	if err != nil {
		return err
	}
	defer unk.Release()
	cat, err := unk.QueryInterface(ole.IID_IDispatch)
	if err != nil {
		return err
	}
	defer cat.Release()
	provider = "Microsoft.Jet.OLEDB.4.0"
	r, err := oleutil.CallMethod(cat, "Create", "Provider="+provider+";Data Source="+f+";")
	if err != nil {
		provider = "Microsoft.ACE.OLEDB.12.0"
		r, err = oleutil.CallMethod(cat, "Create", "Provider="+provider+";Data Source="+f+";")
		if err != nil {
			return err
		}
	}
	r.Clear()
	return nil
}

func main() {
	// Path of databse
	f := "./example.mdb"

	// Remove if it exists
	os.Remove(f)

	// Create a new database and register it
	err := createMdb(f)
	if err != nil {
		fmt.Println("create mdb", err)
		return
	}

	// Connect to database. And everything after this is just ordinary
	// functions from the go standar libray database/sql
	// https://golang.org/pkg/database/sql/
	db, err := sql.Open("adodb", "Provider="+provider+";Data Source="+f+";")
	if err != nil {
		fmt.Println("open", err)
		return
	}
	defer db.Close()

	_, err = db.Exec("create table foo (id int not null primary key, name text not null, created datetime not null)")
	if err != nil {
		fmt.Println("create table", err)
		return
	}

	tx, err := db.Begin()
	if err != nil {
		fmt.Println(err)
		return
	}
	stmt, err := tx.Prepare("insert into foo(id, name, created) values(?, ?, ?)")
	if err != nil {
		fmt.Println("insert", err)
		return
	}
	defer stmt.Close()

	for i := 0; i < 1000; i++ {
		_, err = stmt.Exec(i, fmt.Sprintf("こんにちわ世界%03d", i), time.Now())
		if err != nil {
			fmt.Println("exec", err)
			return
		}
	}
	tx.Commit()

	rows, err := db.Query("select id, name, created from foo")
	if err != nil {
		fmt.Println("select", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		var created time.Time
		err = rows.Scan(&id, &name, &created)
		if err != nil {
			fmt.Println("scan", err)
			return
		}
		fmt.Println(id, name, created)
	}
}

[LeMario (Mario Giesel)]

Wow, Johan!

This is really helpful and very kind.
I’ll give it a try.

So thanks a lot for your comments.
Mario