Creating Access Database? 1

[strebor]

I'm trying to determine if there is a way to create an access database and then create a table within the newly created database using vbscript? I know how to connect to an existing database and add, delete, modify records in a table, but I would like to be able to create a new database from a script.

strebor

 

[rjoubert]

I know you can create a text file with vbscript...I wonder if you can create an MDB file instead of a TXT file. Check out the first block of code on this page...

http://www.devguru.com/technologies/vbscript/quickref/filesystemobject.html

 

[strebor]

Thanks rjoubert... I know about creating text files, and that you can create an excel file or a word file... but, there is something more complicated about an .mdb file; it is essentially the database. When you start Access... You must either open an existing database or create a new one.

Here is a bit of code that is part of my puzzle. It errors on the provider line:

Dim fileParam
Dim strMDB
Dim oParams

Set oParams=WScript.Arguments.Named

fileParam = oParams.Item("File")
strMDB = oParams.Item("MDB")


Dim cat
Set cat = CreateObject("ADOX.Catalog.2.8")
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strMDB & ";"

WScript.echo "Database Created Successfully"
Set cat = Nothing

I copied this snippet of code from the Log Parser Toolkit. My goal is to use vbScript to read a huge log file, converting an unusable date format, and placing the results in a new .mdb file. I'm working on a front end Access GUI that I can use to connect to the .mdb and execute a number of queries and generate a number of different reports.

Once I get past this puzzle the rest will be fairly straight forward as I already have most of the code written. I was converting the whole log file into an xml document and then importing it into access... but if I can skip a step it will make the process a lot easier.


strebor

 

[EBGreen]

Simply trying to connect to a provider will not create a non-existent DB as there is nothing there to connect to. There may be a more elegant solution, but the way I have handled this in the past is to make a blank DB in access and keep that .mdb file with my script. When I need to create a new DB I copy that file renaming it to whatever name I need and voila I have a fresh new DB to play with.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[strebor]

Dim objADOXDatabase
Set objADOXDatabase = CreateObject("ADOX.Catalog")

'Create the database file
objADOXDatabase.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=c:\customer.mdb"

This simple solution came from 4guysfromrolla.com. My problem was that there wasn't a path to say where the database was to be created. So, I think I've found the solution I was seeking.

strebor

 

[EBGreen]

Sweet. Thanks for sharing the solution.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]