Importing data in MS access file into Stata

[Sumedha Gupta]

Dear All,
I have a large dataset in an MS access file (.accdb). How may I import this into Stata? I tried using the odbc option but it is not recognizing the password. It gives me an error:

. odbc load "E:\AAPCC_May 2019_ Mowry_Gupta_data\RawData\AAPCC NPDS DATA_MowryGupta_NarcoticExposures_CE_2014_2018_May2019.accdb", table(MowryGupta_NarcoticEx
> posures_CE_2014_2018)
The ODBC driver reported the following diagnostics
[Microsoft][ODBC Microsoft Access Driver] Not a valid password.
SQLSTATE=42000
r(682);

However, I am able to use the same password to open the file in MS Access, so it seems the password is in fact valid. Will be grateful for any help you may be able to offer to help import this file into Stata.
Many thanks,
Sumedha

[Daniel Schaefer]

What you have is essentially an SQL database. There is almost certainly not a way to load an arbitrary relational database into Stata. Instead, I would load the file in MS Access, then use this guide to extract the data I want to excel. Then I would save the extracted data as a .csv and load it into Stata. You may need to write a SQL query to get a single table with the properties you want before you export to excel. Remember, relational databases like this support multiple tables with relationships between the rows across different tables. In Stata, ideally you want a single table. So use the MS Access software to extract only the data you need, then export and move to Stata.

[Daniel Schaefer]

There might be a way to get odbc to work actually, now that I look a little closer. Some quick googling indicates that you may need to install a special driver for .accdb.

https://answers.microsoft.com/en-us/...8-4fd7d2c7c333

Edit: I realize the platform described in the Microsoft thread is not Stata, but I bet the driver they describe is still a dependency for the Stata command.

[Leonardo Guizzetti]

See

help odbc
odbc list

Following the second command, do you see MS Access Database listed as an available ODBC driver? If so, you should be able to connect, but note you will need to include the -password()- connection option. I don't have access to Access on this machine so I cannot post a working example, but I know this is possible from past experience with password-protected Access databases.

On my system, this is what I have:

. odbc list

Data Source Name                   Driver
-------------------------------------------------------------------------------
dBASE Files                        Microsoft Access dBASE Driver (*.dbf, *.ndx
Excel Files                        Microsoft Excel Driver (*.xls, *.xlsx, *.xl
MS Access Database                 Microsoft Access Driver (*.mdb, *.accdb)
-------------------------------------------------------------------------------

If you don't have Office (with Acess) installed on your system, or if you don't see Access as one of the available ODBC drivers, then you might want to try downloading and installing the Office 2016 redistributable driver from Microsoft.

[Joseph Coveney]

it is not recognizing the password. It gives me an error:

That syntax is incorrect for odbc load. It should look more like the following.

local file_name ///
E:\AAPCC_May 2019_ Mowry_Gupta_data\RawData\AAPCC NPDS DATA_MowryGupta_NarcoticExposures_CE_2014_2018_May2019.accdb
local dsn MS Access Database;DBQ=`file_name';PWD=<insert your password here>;
odbc load, table(MowryGupta_NarcoticExposures_CE_2014_2018) dsn("`dsn'")

Be sure to include the semicolon delimiters in the DSN, and surround the local macro with double quotation marks in the dsn() option of odbc load commant.