odbc driver problem

[Matthew Gillman]

Hi,

I am using Stata/IC 13.1 for Windows (32-bit) Revision 12 Nov 2015 on a Windows 8.1 Pro machine.

I would like to use Stata's odbc commands to connect to a Microsoft Access database. In fact I would eventually like to create my own databases through these commands, but first things first.

Following the documentation, when I type:

. odbc list

I correctly see a list of ODBC DSNs. This includes the entry:

MS Access Database Microsoft Access Driver (*.mdb, *.accdb)

When I click on it, or just execute the command:

. odbc query "MS Access Database"

I get this error:

The ODBC driver reported the following diagnostics
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch
between the Driver and Application
SQLSTATE=IM014
r(682);

Some web research quickly revealed that this is probably due to a clash between Stata (32 bit) and MS Access (which I assume is 64 bit).

There are two versions of the "ODBC Data Source Administrator". Both are called odbcad32.exe. The 64-bit version is in C:\Windows\System32, whereas the 32-bit version is in C:\Windows\SysWOW64.

There seems to be a 64-bit driver already available for MS Access. My question is: do I need to create a 32-bit driver for my 32-bit Stata to work? If so, I tried using the 32-bit odbcad32.exe to create a 32-bit version of the driver, but when I attempted to query it in Stata I got this error:

. odbc query "MS Access 32 bit driver"
The ODBC driver reported the following diagnostics
r(682);

So either i shouldn't be creating a 32-bit driver in the first place, or have created it incorrectly, or there is some other problem.

I don't know if it would be possible to switch to 64-bit Stata at my university.

Any help would be greatly appreciated :-)

I have attached a screenshot of the odbc admin dialog for my 32 bit driver attempt. Note that it only lists *.mdb and not *.accdb - is this a problem?

Thanks

Matthew

[Jeph Herrin]

You will have to install 32bit Access,or 64bit Stata.

cheers,
Jeph

[Matthew Gillman]

Update: using C:\Windows\SysWOW64\odbcad32.exe, I was able to create what I think is a 32-bit (*.mdb) empty database. I was then able to do the following in Stata:

. odbc query "32 bit test"

DataSource: 32 bit test
Path : C:\Users\gillma01\Desktop\test32

I was then able to create an empty table in the database:

. odbc exec ("CREATE TABLE Persons (PersonID int, FirstName varchar(255));"), dsn("32 bit test")

Sure enough, the empty table appears in Access with the two columns specified (see screenshot).

Ultimately it would be nice to just create the databases directly from Stata, e.g. in a do-file, rather than having to use odbcad32.exe. Is this possible?

Thanks

Matthew

[Matthew Gillman]

I get an error when trying to insert sample data from Stata. This is probably because I am new to Stata. The command and subsequent error are:

. clear all

. gen PersonID=1

. gen FirstName=("Matt")

. odbc insert PersonID FirstName, table("Persons") dsn ("32 bit test")
no observations

.

What do I need to do here? (NB I tried this before, having used the auto dataset immediately beforehand (74 observations), and it created 74 identical entries in the Persons table - i.e. all with PersonID 1 and First Name "Matt".)

[Matthew Gillman]

Another update: this time I manually entered some data into Stata and it successfully wrote it to the Access database:

. clear all
. input str16 name age str6 sex
name age sex
1. "Fred Smith" 45 male
2. "Natasha Jones" 44 female
3. end
. odbc exec ("CREATE TABLE People (name varchar(255), age int, sex varchar(255));"), dsn("32 bit test")
. odbc insert name age sex, table("People") dsn ("32 bit test")

This successfully populated the new People table (checked by viewing in Access).