+ Reply to Thread
Results 1 to 3 of 3

Check to see if Database has been created

Hybrid View

  1. #1
    Registered User
    Join Date
    07-25-2008
    Location
    England
    Posts
    8

    Check to see if Database has been created

    Ive come across some code which will create a database in Access from Excel which will copy all the data from the excel spreadsheet to a table in the database. The database is created and named the same name as the excel spreadsheet, the only problem with this code is that when it spots that the database has already been created, it throws an error.

    Here is the code

    Sheets("Header").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
    "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"
    filename = Range("C2").Value
    Range("C2").ClearContents
    
    
        Dim dbConnectStr As String
        Dim Catalog As Object
        Dim cnt As ADODB.Connection
        Dim dbPath As String
    
        'Set database name here
        dbPath = "H:\maginus\Database\Reporting\Rebates\Sell Out or In Allowance\" & filename & ".mdb"
        dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
    
        'Create new database
        Set Catalog = CreateObject("ADOX.Catalog")
        Catalog.Create dbConnectStr
        Set Catalog = Nothing
    
        'Connect to database and insert a new table
        Set cnt = New ADODB.Connection
        With cnt
            .Open dbConnectStr
            .Execute "CREATE TABLE SOA ([Customer Account] text(50), " & _
                     "[Customer Name] text(150), " & _
                     "[Product Code] text(50), " & _
                     "[Product] text(50), " & _
                     "[Quantity] text(50), " & _
                     "[Claim] text(50), " & _
                     "[Date] text(50))"
        End With
        Set cnt = Nothing
    Now i want to to look to see if the database has been created, if it has, run the above code to create it, if it is already there, skip the creation part and continue to the next part that enters in the required data (not shown above)

    Any ideas how to get Excel to check if the database has been created in Access?

    Many Thanks

    Dave

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    This is untested but try adding an error handler

      Sheets("Header").Select
        Range("C2").Select
        ActiveCell.FormulaR1C1 = _
        "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"
        Filename = Range("C2").Value
        Range("C2").ClearContents
    
    
        Dim dbConnectStr As String
        Dim Catalog As Object
        Dim cnt    As ADODB.Connection
        Dim dbPath As String
    
        'Set database name here
        dbPath = "H:\maginus\Database\Reporting\Rebates\Sell Out or In Allowance\" & Filename & ".mdb"
        dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
        On Error Resume Next
        'Create new database
        Set Catalog = CreateObject("ADOX.Catalog")
        Catalog.create dbConnectStr
        Set Catalog = Nothing
    
        'Connect to database and insert a new table
        Set cnt = New ADODB.Connection
        With cnt
            .Open dbConnectStr
            .Execute "CREATE TABLE SOA ([Customer Account] text(50), " & _
                     "[Customer Name] text(150), " & _
                     "[Product Code] text(50), " & _
                     "[Product] text(50), " & _
                     "[Quantity] text(50), " & _
                     "[Claim] text(50), " & _
                     "[Date] text(50))"
        End With
        On Error GoTo 0
        Set cnt = Nothing
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    07-25-2008
    Location
    England
    Posts
    8
    Ah, that works great

    Thank you very much

    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1