[Webinar] Streamline your web hosting managementRegister Today


ADO equivalent for creating access database

Posted on 1999-12-03
Medium Priority
Last Modified: 2013-12-25
I have this DAO code that I used to create and load an access database into my application when the application opens.  I was wondering if someone could tell me how to do the same thing using ADO.  Thanks.

Set ws = DBEngine.Workspaces(0)
Set db = ws.CreateDatabase(Userfile, dbLangGeneral, dbVersion30)
Set db = ws.OpenDatabase(Userfile)
Set td = db.CreateTableDef("ValExp")
With td
    .Fields.Append .CreateField("Stock Name", dbText, 40)      '0
    .Fields.Append .CreateField("Stock Symbol", dbText, 6)      '1
    .Fields.Append .CreateField("Stock Price", dbSingle)        '2
    .Fields.Append .CreateField("Est Date", dbDate)             '3
    .Fields.Append .CreateField("Est Year", dbText, 6)          '4
    .Fields.Append .CreateField("Price High(0)", dbSingle)      '5
    .Fields.Append .CreateField("Price High(1)", dbSingle)      '6
    .Fields.Append .CreateField("Price High(2)", dbSingle)      '7
    .Fields.Append .CreateField("Price High(3)", dbSingle)      '8
    .Fields.Append .CreateField("Price High(4)", dbSingle)      '9
    .Fields.Append .CreateField("Price High(5)", dbSingle)      '10
End With

db.TableDefs.Append td

Set rs = db.OpenRecordset("ValExp", dbOpenDynaset)

End Sub
Question by:jfyfe
  • 2
LVL 43

Accepted Solution

TimCottee earned 600 total points
ID: 2252979
Try this, it might get you started, you will probably need to sort out some of the bits, but it does sort of work. The main problem I had doing this was that when you try to open this directly in access it doesn't like the database format ( I don't know why!)

Let me know how you get on though.

    Dim catAccess As New ADOX.Catalog
    Dim tblValExp As New ADOX.Table
    Dim cnnAccess As New ADODB.Connection
    Dim rstValExp As New ADODB.Recordset
    catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
    catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
    With tblValExp
        .Name = "tblValExp1"
        .Columns.Append "Stock Name", adVarWChar, 40
        .Columns.Append "Stock Symbol", adVarWChar, 6     '1
        .Columns.Append "Stock Price", adSingle       '2
        .Columns.Append "Est Date", adVarWChar, 10           '3
        .Columns.Append "Est Year", adVarWChar, 6         '4
        .Columns.Append "Price High(0)", adSingle     '5
        .Columns.Append "Price High(1)", adSingle     '6
        .Columns.Append "Price High(2)", adSingle     '7
        .Columns.Append "Price High(3)", adSingle     '8
        .Columns.Append "Price High(4)", adSingle     '9
        .Columns.Append "Price High(5)", adSingle     '10
    End With
    catAccess.Tables.Append tblValExp
    Set tblValExp = Nothing
    Set catAccess = Nothing
    cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security Info=False"
    rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
    rstValExp.Fields(0).Value = "Name"
    rstValExp.Fields(1).Value = "Symbol"
    rstValExp.Fields(2).Value = 1.5
    rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
    rstValExp.Fields(4).Value = "2000"
    rstValExp.Fields(5).Value = 1.5
    rstValExp.Fields(6).Value = 1.5
    rstValExp.Fields(7).Value = 1.5
    rstValExp.Fields(8).Value = 1.5
    rstValExp.Fields(9).Value = 1.5
    rstValExp.Fields(10).Value = 1.5
    Set rstValExp = Nothing
    Set cnnAccess = Nothing

Author Comment

ID: 2253359
Thanks for answering so quickly.  Didn't know I could use ADOX (or even what is was) until now.  Your code along with http://msdn.microsoft.com/library/officedev/off2000/achowDAOToADOX.htm did the trick.

Author Comment

ID: 2273097
Tim, don't know if you figured it out, but I think by adding "Jet OLEDB:Engine Type=4" to the Create statement, it will allow you to open the database in Access.

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & Userfile & ";" & _
                       "Jet OLEDB:Engine Type=4;"

I also have a pending question about how to allow null records.  Can you help?

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question