Use VBScript to get installed DAO DbEngine object

[Isaac]

Was I wrong to put this in this forum? I hope you will have a little grace with me on that, as this has to do with 2 things at once...yes VBScript (for which I saw no forum), but also good old DAO, so posting it here ...

I am trying to use VBScript to get the installed version of DAO DBEngine object. Does this look like it might work for a user base which is split between having 1) Access 2010 and 2) Access 2016 ? I can only test with Access 2016

Function DAO_DBEngine()

On Error Resume Next
'try Access 2007 (DAO Engine version 120)
Set GetDBEngine = CreateObject("DAO.DBEngine.120")
If Err.Number <> 0 Then
    'try DAO version 3.6
    Err.Clear
    Set GetDBEngine = CreateObject("DAO.DBEngine.36")
    If Err.Number <> 0 Then
        Set GetDBEngine = CreateObject("DAO.DBEngine.35")
    End If
End If

End Function

It is my understanding from research that Set DBEngine = CreateObject("DAO.DBEngine") will not work any more.
Grateful for any and all input from anyone who is knowledgeable about this.

 

[June7]

I have Access 2010. I put code in a text file and renamed with vbs extension. The script executes on double click. So, what happens now? What are you trying to accomplish?

 

[Isaac]

I think the test would be more if you tried to use the function. I need to create an access database using vbscript. I was also thinking of using an Access application object, (like set objAccess = createobject("Access.Application"), and then using objAccess.dbEngine..............

But when I do that, I get "invalid argument" on the CreateDatabase line

set objAccessApp = createobject("Access.Application")
objAccessApp.DBEngine.CreateDatabase strDBPath, db_lang_general

(I have already tested that strDBPath is a legitimate path, like C:\Users\mua3d38\AppData\Roaming\FlipSharepoint\Sharepoint.accdb

 

[Isaac]

I think I have just made some progress. the Invalid ARgument on my last attempt is because db_Lang_General needs quotes - it means nothing to vbscript.

 

[Isaac]

And now I am making progress, I now am using:

set objAccessApp = createobject("Access.Application")
objAccessApp.DBEngine.CreateDatabase strDBPath, "db_lang_general"

and I get "could not find installable isam". Trying to research that now.

 

[June7]

You want to programmatically create database? Review https://www.excelguru.ca/content.php?122-Creating-an-Access-Database-(on-the-fly)-Using-VBA-and-SQL


You said you need to use VBScript, so does that mean you want code in a .vbs file?

 

[Gasman]

Seems for ADO it is quite easy for a mdb file?

https://www.motobit.com/tips/detpg_createmdb/

 

[Isaac]

I hadn't tried the ADOX catalog method yet, but let me try it. Thanks

 

[June7]

And these

How can a blank MS Access database be created using VBA?

I'm a total noob trying to create a blank MS Access database using VBA in Excel. I want to name the new database "tblImport". This is the code I´m using: Sub makedb() Dim accessApp As Access.

stackoverflow.com

VBA, Create New Access Database From Excel - VBA and VB.Net Tutorials, Education and Programming Services

In this article I will explain how you can create a new (blank) access database from Excel. There are 2 methods for getting this done: Late Binding Early Binding In early binding we add a reference to the Access Object Library. The benefit of using early binding

software-solutions-online.com

 

[Isaac]

I'm using vbscript, not VBA

 

[Isaac]

I will try the late binding methods you mentioned..

 

[cheekybuddha]

Hi Isaac,

>> because db_Lang_General needs quotes <<

This is wrong. db_Lang_General is a constant and will have a numeric value.

Open a copy of Access and in the Immediate Window (Ctrl+G) type: ?db_Lang_General and hit Enter.

It should give you the value you should use. Use it directly, or declare the constant in your script.

The same applies for all constraints of external libraries like DAO or ADO, which vbScript knows nothing about.

 

[Isaac]

Hi All,
Since the last time I posted I have gone with the access application object .NewCurrentDatabase which worked like a charm!

However, I appreciate your post...the error message changing after I changed it to quotes mislead me to believe that I had made a certain type of progress. Which I guess was wrong. I had also been thinking that I needed the numeric value and looked on Microsoft but could not find a listing of the constants-to-numeric values like they have for other things.
I did NOT know that the immediate window would return the numeric value - I have always looked them up on BOL. Thank you !!! Learned something useful here.

 

[Isaac]

Someone after my last post I also realized I needed to switch acLinkSharePointList to 1, I guess that should have clued me in to the dbLangGeneral, too. Thanks again .

 

[June7]

Could you post the final working procedure?

 

[Isaac]

Sure, June.
It's part of a much larger VBScript, but here are the relevant parts:

Set wshShell = CreateObject("WScript.Shell")
strUserName = wshShell.ExpandEnvironmentStrings("%USERNAME%")
strDBFolder = wshShell.ExpandEnvironmentStrings("%APPDATA%") & "\FlipSharepoint"
strDBFolder = replace(strDBfolder,"\\","\") 'in case wshShell returns extra \
strDBName = "Flip Sharepoint Column.accdb"
strDBPath = strDBFolder & "\" & strDBName
set objAccessApp = createobject("Access.Application")
objAccessApp.NewCurrentDatabase strDBPath

 

[cheekybuddha]

Turns out those constants are strings after all which is why there was probably no error.

See here: Language Constants Enumeration

hth,

d

 

[June7]

Since I don't have larger procedure nor SharePoint, I distilled down to two lines.

set objAccessApp = createobject("Access.Application")
objAccessApp.NewCurrentDatabase "C:\Users\June\Forums\test.accdb"

 

[Isaac]

Turns out those constants are strings after all which is why there was probably no error.

See here: Language Constants Enumeration

hth,

d

I see. So maybe no invalid argument error, but probably what led to the ISAM error, since it couldn't find any such language pack (or something like that) ?

 

[arnelgp]

dim wshShell, strUserName, strDBFolder
dim strDBName, strDBPath

Set wshShell = CreateObject("WScript.Shell")
strUserName = wshShell.ExpandEnvironmentStrings("%USERNAME%")
strDBFolder = wshShell.ExpandEnvironmentStrings("%APPDATA%") & "\FlipSharepoint"
strDBFolder = replace(strDBfolder,"\\","\") 'in case wshShell returns extra \
strDBName = "Flip Sharepoint Column.accdb"
strDBPath = strDBFolder & "\" & strDBName

Dim objADOX

Set objADOX = CreateObject("ADOX.Catalog")
objADOX.Create "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strdbpath
MsgBox objADOX.Tables.count & " tables."

Set objADOX = Nothing