Using Access Database Without Having MS Access

[grnzbra]

I have seen an application that uses an Access database without the user's computer needing to have MS Access installed. Once someone has created the database in MS Access, how do they create this version of the database?

 

[Andrzejek]

Do you mean: How to Open MDB File without Microsoft Access?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[grnzbra]

No. How does one create it?

 

[Andrzejek]

So the question is:
How does one create an Access data base without Access installed?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[grnzbra]

Not quite. How does one, using MS Access create an Access database that can be used on a computer that does not have MS Access installed?

 

[strongm]

You don't have to do anything special in Access.

 

[combo]

Access database is in fact JET/ACE database with Access UI, macros and VBA.
So if you need to use all features stored in the file, you need Access Runtime installed, however in this case there is no access to the design.

The database can be generated programmatically with ADOX and ADO libraries, my very old support code (maybe found somewhere) to create 'mdb' file:

Sub CreateMdbDatabase()
Dim adxCat As ADOX.Catalog
Dim adxTable As ADOX.Table
Dim Conn

' ADOX: create database
Set adxCat = New ADOX.Catalog
adxCat.Create ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "DataBasePathAndName.mdb" & ";" & _
        "Jet OLEDB:Engine Type=5")
Conn = adxCat.ActiveConnection

' ADOX: create table
Set adxTable = New ADOX.Table
With adxTable
    .Name = "Table1"
    With .Columns
        .Append "Field1", adDate ' date
        .Append "Field2", adSmallInt ' integer
        .Append "Field3", adUnsignedTinyInt ' byte
        .Append "Field4", adVarWChar, 1 ' string, field size=1
        .Append "Field5", adInteger ' long integer
        .Append "Field6", adDouble ' double
        .Append "Field7Optional", adVarWChar, 1 ' string, field size=1
        .Item("Field7Optional").Attributes = adColNullable ' allow null
    End With
End With
adxCat.Tables.Append adxTable
Set adxTable = Nothing

' ADO: access to table 1
Dim adoRs As ADODB.Recordset
Set adoRs = New ADODB.Recordset
adoRs.Open _
    Source:="Table1", _
    ActiveConnection:=Conn, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockPessimistic, _
    Options:=adCmdTable

' fill "Table1"
   
adoRs.Close
Set adoRs = Nothing
Set adoRs = Nothing
End Sub

As long as you don't access UI and macros, it is possible to work with data without Access.

combo

 

[grnzbra]

Thank you very much. "Runtime" - that's what I couldn't remember.
Is the following done for each table in the database?

' ADOX: create table
Set adxTable = New ADOX.Table
With adxTable
    .Name = "Table1"
    With .Columns
        .Append "Field1", adDate ' date
        .Append "Field2", adSmallInt ' integer
        .Append "Field3", adUnsignedTinyInt ' byte
        .Append "Field4", adVarWChar, 1 ' string, field size=1
        .Append "Field5", adInteger ' long integer
        .Append "Field6", adDouble ' double
        .Append "Field7Optional", adVarWChar, 1 ' string, field size=1
        .Item("Field7Optional").Attributes = adColNullable ' allow null
    End With
End With
adxCat.Tables.Append adxTable
Set adxTable = Nothing

' ADO: access to table 1
Dim adoRs As ADODB.Recordset
Set adoRs = New ADODB.Recordset
adoRs.Open _
    Source:="Table1", _
    ActiveConnection:=Conn, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockPessimistic, _
    Options:=adCmdTable

 

[combo]

Yes, each table has to be designed and added to the database. More examples of db details created with ADOX:

http://allenbrowne.com/func-ADOX.html

combo

 

[grnzbra]

Thank you very much.