Date:         Wed, 18 Feb 2009 15:36:11 -0500
Reply-To:     "Lamias, Mark (CDC/CCID/OD) (CTR)" <bnz6@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Lamias, Mark (CDC/CCID/OD) (CTR)" <bnz6@CDC.GOV>
Subject:      Re: Can you create a new Access database from SAS?
Comments: To: "Norris, Paul" <Paul.Norris@UCSF.EDU>
In-Reply-To:  <15B611C1F7985144A398CD874F0864FD056CF059@EXVS05.net.ucsf.edu>
Content-Type: text/plain; charset=us-ascii

Proc export will do this for you.  If you can't use proc export for some
reason, I would suggest creating a VB script that accepts the full
pathname to the access database you want to create and then call the vbs
script via the x command from sas to create the database.  Once created,
you could export your data to it via traditional SAS methods.

Here is an example of some VB Script that will do the trick (I borrowed
the original script from
http://myitforum.com/articles/11/view.asp?id=459 and modified it to
accept a command line argument):

==============================================
Sub CreateDB (path)
Dim cat
Set cat = WScript.CreateObject("ADOX.Catalog")
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";"
Set cat = Nothing
End Sub

'Dim path
'path = InputBox("Type the path of the database to create.", "Database
Path", "C:\mydb.mdb")

Dim strFilename, strCommand
path= WScript.Arguments(0)


Dim fso
Set fso = WScript.CreateObject("Scripting.Filesystemobject")
If fso.FileExists(path) Then
If MsgBox ("A file already exists at this location. Do you want to
replace it?", vbOKCancel Or vbExclamation, "Warning") = vbOK Then
fso.DeleteFile path
CreateDB path
End If
Else
CreateDB path
End If
==============================================

You should copy this script and paste it into notepad and save it as
"C:\CreateAccess.vbs."  Then you would write your SAS program as
follows:

options xwait noxsync;  /*these may need to be changed for your
program*/

%let NEW_DB_Path =c:\MyNewAccessDB.mdb; /*provide the full path to your
new mdb*/

x "c:\CreateAccess.vbs &NEW_DB_Path";   /*create the DB*/

I hope this helps.

Sincerely yours,

Mark J. Lamias
SAIC Statistical Consultant
Office of Informatics
National Center for Preparedness, Detection, and Control of Infectious
Diseases
Coordinating Center for Infectious Diseases
US Centers for Disease Control and Prevention
w: (404) 498-2646
m: (404) 543-1394
f: (404) 638-5615


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Norris, Paul
Sent: Wednesday, February 18, 2009 3:05 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Can you create a new Access database from SAS?

Is it possible to create a new Access database from SAS?

I have a client for whom I create a delivery in the form of an Access
database every month, and it would be helpful to be able to create the
delivery vehicle in SAS, as opposed to just loading data into it.

Thanks,

Paul Norris
UCSF