none
DBEngine.CreateDatabase RRS feed

  • Question

  • How to  create an Access-database i VB 2019

    There is method DBEngine.CreateDatabase, I guess that's the one to use.

    I have tried this code and I have referenced DAO 3.6

    Dim dbNew As DAO.Database = DBEngine.CreateDatabase(Dbase, dbLangSwedFin, dbVersion30)

    But no luck.

    There ought to be someone who knows, please help, how to use Createdatabase


    Appleforce

    Saturday, May 16, 2020 5:53 AM

Answers

  • Well I think eventually I answered my own question.

    It is now possible to create new databases and after e few days of debugging, well OK not really debugging, more like 'Boy do as you are told', debugging like 'Naming rule violation: These words must begin with upper case characters', '#Disable Warning IDE0054 ' Use compound assignment' and Readonly assignment.

    I think I have my project working again. So I'm happy, happy, happy. Of course there are other things to resolve and make better and so on and so forth.



    Appleforce

    • Marked as answer by appleforce Saturday, June 6, 2020 9:50 AM
    Saturday, June 6, 2020 9:50 AM

All replies

  • After some testing I have found out that is possible to create new databases in a new projekt (VB2019), it is even possible to create tables in the database using code from the old (VB2010) project.

    But in the old project (VB2010) loaded in VB2019 it is not.

    This is the simple code used:

    Dim dbe = New DAO.DBEngine()
    Dim database = dbe.CreateDatabase(Dbase, DAO.LanguageConstants.dbLangSwedFin,     DAO.DatabaseTypeEnum.dbVersion40)
    database.Close()

    I thought that an old project loaded in VB2019 would automatically be transformed to work there. OK, most things work, but creating databases do not.

    What am I missing? What can bo done? Anyone? 


    Appleforce



    • Edited by appleforce Sunday, May 24, 2020 7:17 AM
    Sunday, May 24, 2020 7:12 AM
  • I thought that an old project loaded in VB2019 would automatically be transformed to work there. OK, most things work, but creating databases do not.

    Which error is returned ?

    I tested with VS 2015 and Access databases are created without any problem, with DAO or other methods

    Sunday, May 24, 2020 10:43 AM
  • Hi,
    in VS2019 I use following code to create Access database file without problems:

          Dim cat As New ADOX.Catalog
          Dim dbPath = "c:\temp\x.mdb"
          Dim cnString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={dbPath};Locale Identifier=1053;Jet OLEDB:Engine Type=5"
          cat.Create(cnString)

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Sunday, May 24, 2020 1:17 PM
  • Error # 5

    Could not load file or assembly 'Interop.DAO, Version=5.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencie. This assembly is of a later version ...


    Appleforce

    Monday, May 25, 2020 6:08 AM
  • OK, thanks.

    Even this piece of code will create a database in a new VB2019 project,

    but it is not working in the old project VB2010 loaded in VB2019

    Error #91

    Could not load file or assembly 'Interop.ADOX, Version=6.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. 


    Appleforce

    Monday, May 25, 2020 6:51 AM
  • Hi,
    open old VS2010 project in VS2019, remove all ADO references, include actual ADO references.



    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Monday, May 25, 2020 7:30 AM
  • OK, no ADOX, no DAO.

    Code:

            Dim database = DBEngine.CreateDatabase(Dbase, LanguageConstants.dbLangSwedFin, DatabaseTypeEnum.dbVersion40)
            database.Close()

    No succes

    Error # 91
    "Objektvariabel or With-blockvariabel not set."


    Appleforce


    • Edited by appleforce Tuesday, May 26, 2020 12:35 PM
    Tuesday, May 26, 2020 9:39 AM
  • Here is an idea, create a blank database (or have pre-defined tables and data) e.g. name it Database1.accdb, place it in the root folder of the project. Now under post-build events add the following.

    Note here I use a .accdb but the same applies for .mdb

    Under project properties, compile tab, click "Build Events" button.

    if not exist $(TargetDir)\Database mkdir $(TargetDir)\Database
    xcopy "$(ProjectDir)*.accdb" "$(TargetDir)Database" /Y


    In code copy the database when needed then if you need to create dynamic tables and data do so.

    Imports System.IO
    
    Public Class Form1
        Private Async Sub CopyDatabaseButton_Click(sender As Object, e As EventArgs) _
            Handles CopyDatabaseButton.Click
    
            Dim databaseFile = New FileInfo(
                Path.Combine(
                    AppDomain.CurrentDomain.BaseDirectory, "Database", "Database1.accdb"))
    
            Dim destinationPath =
                    Path.Combine(AppDomain.CurrentDomain.BaseDirectory, databaseFile.Name)
    
            Try
                If File.Exists(destinationPath) Then
                    File.Delete(destinationPath)
                End If
                databaseFile.CopyTo(destinationPath)
    
                '
                ' slight pause to ensure the copy has fully completed and
                ' consider this completely optional
                '
                Await Task.Delay(1000)
    
                CreateTables()
    
            Catch ex As Exception
                If ex.Message.Contains("The process cannot access the file") Then
                    MessageBox.Show($"{databaseFile.Name} is currently in use, please close and try again")
                Else
                    MessageBox.Show(ex.Message)
                End If
    
            End Try
        End Sub
        Private Sub CreateTables()
            '
            ' Using OleDb create any tables needed and if
            ' needed populate with data
            '
    
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 26, 2020 3:23 PM
    Moderator
  • Karen, thanks for your suggestion, I have thought of a solution like this, but I can't say I like it, feels like a way I don't like to go.

    Instead I have created a new project, I have imported all Classes and Forms from the old project and, to be sure, I have created new Moduls an copied the text from the old project into these new Moduls.

    Yes, it took a while and after a session of debugging, it seems to work, it is possible to create new databases using CreateDatabase.

    And furthermore, I can reference both ADOX and DAO, no problem. (This is a comment to suggestions above.)

    So now I hope my long time project, started with VB1 in 1992, will  survive and prosper some years to come.

    I guess there must be someone somewhere who can explain the problems I have met upgrading to VB2019.


    Appleforce

    Wednesday, May 27, 2020 11:23 AM
  • Be aware, with Adox and Dao you cannot make a Accdb file only a Jet file (mdb) (a jet file also used with FoxPro was popular called an access file since FoxPro was gone). 

    Use the reference Peter has given and than this code

            Dim db As New ADOX.Catalog
            db.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\test.mdb;Locale Identifier=1053;Jet OLEDB:Engine Type=5")


    Success
    Cor


    Wednesday, May 27, 2020 1:49 PM
  • Thanks for the remark.
    But as the project is going on since 1992 the jet-database is the only choice of compatibilty reasons.


    Appleforce

    Wednesday, May 27, 2020 2:54 PM
  • Hi appleforce,

    You can consider posting it at the following forum for more help about his problem. 

    https://developercommunity.visualstudio.com/spaces/61/index.html

    Thanks!

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 29, 2020 8:52 AM
    Moderator
  • Well I think eventually I answered my own question.

    It is now possible to create new databases and after e few days of debugging, well OK not really debugging, more like 'Boy do as you are told', debugging like 'Naming rule violation: These words must begin with upper case characters', '#Disable Warning IDE0054 ' Use compound assignment' and Readonly assignment.

    I think I have my project working again. So I'm happy, happy, happy. Of course there are other things to resolve and make better and so on and so forth.



    Appleforce

    • Marked as answer by appleforce Saturday, June 6, 2020 9:50 AM
    Saturday, June 6, 2020 9:50 AM