Data Control to ADO Code

Posted on 2007-07-26
Last Modified: 2010-04-09
How would you turn this into ADO code?

If Data1.Database.TableDefs("DBRecords").Name <> "DBRecords" Then create_dbrecords
If Data1.Database.TableDefs("musicians").Name <> "musicians" Then musicians
If Data1.Database.TableDefs("all_users").Fields("Birthday").Name <> "Birthday" Then UpgradeMDB
If Data1.Database.TableDefs("all_users").Fields("Music").Name <> "Music" Then UpgradeMDBII
If Data1.Database.TableDefs("email_offline").Name <> "email_offline" Then Email_Offline
If Data1.Database.TableDefs("event_invite").Name <> "event_invite" Then Event_Invite
If Data1.Database.TableDefs("advert_via_event_log").Name <> "advert_via_event_log" Then advert_via_event_log
If Data1.Database.TableDefs("live_shows").Name <> "live_shows" Then live_shows
Question by:Takamine334
    LVL 11

    Expert Comment

    You would need to create a database object, open it to the correct database, and then search and replace "Data1.Database" with the name od the database object:

    Dim MyDB As Database
    Set MyDB = OpenDatabase("c:\myAccessDB.mdb")
    If MyDB.TableDefs("DBRecords").Name <> "DBRecords" Then create_dbrecords
    If MyDB.TableDefs("musicians").Name <> "musicians" Then musicians
    If MyDB.TableDefs("all_users").Fields("Birthday").Name <> "Birthday" Then UpgradeMDB
    If MyDB.TableDefs("all_users").Fields("Music").Name <> "Music" Then UpgradeMDBII
    If MyDB.TableDefs("email_offline").Name <> "email_offline" Then Email_Offline
    If MyDB.TableDefs("event_invite").Name <> "event_invite" Then Event_Invite
    If MyDB.TableDefs("advert_via_event_log").Name <> "advert_via_event_log" Then advert_via_event_log
    If MyDB.TableDefs("live_shows").Name <> "live_shows" Then live_shows

    Author Comment

    oh, that's it? for ADO code?
    LVL 11

    Expert Comment

    Oh, heck.  What was I thinking...

    Sorry about that.  It's late in the day.  :(

    Author Comment

    So, thats not the answer? Guess not.
    LVL 12

    Accepted Solution

    Use both ADO and ADOX to accomplish this.

    1. Add "Microsoft ActiveX Data Objects 2.x Library" and "Microsoft ADO Ext. 2.x for DDL and Security" via the Projects->References menu.

    2. Copy sample code below into a new project and run it.

    Option Explicit

    Private Sub Form_Load()

       Dim oCN As New ADODB.Connection
       Dim oRS As New ADODB.Recordset
       With oCN
          .Provider = "Microsoft.JET.OLEDB.4.0"
          .Properties("Data Source") = "C:\YOURDB.MDB"       '<== CHANGE THIS TO YOUR ACTUAL DB NAME AND PATH
       End With
       If oCN.State = adStateOpen Then
          showTables oCN
       End If

    End Sub

    Private Sub showTables(oCN As ADODB.Connection)

       Dim oCat As New ADOX.Catalog
       Dim oTbl As New ADOX.Table
       oCat.ActiveConnection = oCN
       '--- For Each oTbl In oCat.Tables
       '---   Debug.Print oTbl.Name, oTbl.Type
       '--- Next
       If oCat("DBRecords").Name <> "DBRecords" Then Debug.Print "create_dbrecords"
       If oCat("musicians").Name <> "musicians" Then  Debug.Print "musicians"
       If oCat("all_users").Columns("Birthday").Name <> "Birthday" Then  Debug.Print "UpgradeMDB"
       If oCat("all_users").Columns("Music").Name <> "Music" Then  Debug.Print "UpgradeMDBII"
       If oCat("email_offline").Name <> "email_offline" Then  Debug.Print "Email_Offline"
       If oCat("event_invite").Name <> "event_invite" Then  Debug.Print "Event_Invite"
       If oCat("advert_via_event_log").Name <> "advert_via_event_log" Then  Debug.Print "advert_via_event_log"
       If oCat("live_shows").Name <> "live_shows" Then  Debug.Print "live_shows"
    End Sub
    LVL 14

    Assisted Solution

    Dim myConn
    Set myConn = Server.CreateObject("ADODB.Connection")
    myConn.Open = ("DRIVER={Microsoft Access" &_
    " Driver (*.mdb)};DBQ=" &_

    set rs = server.createObject("ADODB.Recordset") strSQL, db, 2, 2

    If rs("COLUMN_NAME") <> "CONDITION" Then create_dbrecords

    set rs=nothing

    Set myConn = nothing


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    In this tutorial viewers will learn how to code links for mobile sites that, once clicked, send a call or text to a specified number. For a telephone link (once clicked, calls a number), begin with a normal "<a href=" link tag. For the href, specify…
    In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now