none
Creating new access database everyday at midnight RRS feed

  • Question

  • im trying to get the program to create a access database everyday at midnight which it does create one at midnight but if there is already a database active it will not create one.

        Dim fileDateTime As String = Date.Now.ToString(" MM-dd-yyyy")

        Dim dir As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)

        Dim FullFileName As String = dir & "\DataBase\GuessesDB"

        Dim databaseName As String = FullFileName & fileDateTime & ".mdb"

        Dim ConString As String = ("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & databaseName)

        Dim TableName As String = "GuessTable"

        Dim acatalog As New Catalog

        Dim atable As New Table

        Dim aindex As New Index

    Private Sub CreateDB()

            On Error GoTo errhandler

            acatalog.Create(ConString)

            On Error Resume Next

            acatalog.ActiveConnection = (ConString)

            atable.Name = (TableName)

            atable.Columns.Append("CardID", DataTypeEnum.adVarWChar, 40)

            atable.Columns.Append("Guesses", DataTypeEnum.adVarWChar, 40)

            acatalog.Tables.Append(atable)

            aindex.Name = ("ColIndex")

            aindex.IndexNulls = AllowNullsEnum.adIndexNullsAllow

            aindex.PrimaryKey = False

            aindex.Unique = True

            aindex.Columns.Append("CardID")

            atable.Indexes.Append(aindex)

    errhandler:

            If Err.Number = -2147217897 Then

                MsgBox("Database already exists")

            ElseIf Err.Number <> 0 Then

               'Do Nothing

            End If

            atable = Nothing

            aindex = Nothing

            acatalog = Nothing

        End Sub

        Private Sub DBTimer_Tick(sender As Object, e As EventArgs) Handles DBTimer.Tick

            If Hour(Now) = 0 And Minute(Now) = 0 Then

                CreateDB()

            End If

        End Sub

        Private Sub DataBaseBTN_Click(sender As Object, e As EventArgs) Handles DataBaseBTN.Click

            DBTimer.Start()

        End Sub



    Thursday, March 7, 2019 8:27 PM

All replies

  • Hello,

    It's not possible to create a new access database while the current one is opened as you have already learned and the only way around this is to first close that database currently opened. So if the database is not currently opened by your app there is no way safely to close it unless you are sure there is nothing going on presently. If nothing is going on and not part of your app you would need to something like

    For Each p As Process In System.Diagnostics.Process.GetProcessesByName("msaccess")
    	Try
    		p.Kill()
    		p.WaitForExit() ' possibly with a timeout
    	Catch winException As Win32Exception
    		' process was terminating or can't be terminated - deal with it
    	Catch invalidException As InvalidOperationException
    		' process has already exited - might be able to let this one go
    	End Try
    Next p


    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

    Thursday, March 7, 2019 8:35 PM
    Moderator
  • Thank you for your reply. msaccess isn’t open so killing msaccess doesn’t work but I believe its the .ldb that’s stopping the creation of a new database. How do I close the .ldb?

    Friday, March 8, 2019 10:39 PM
  • Thank you for your reply. msaccess isn’t open so killing msaccess doesn’t work but I believe its the .ldb that’s stopping the creation of a new database. How do I close the .ldb?

    Try deleting it which will work if there is not a lock on the lock file which there should not be if the database closed properly.


    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

    Friday, March 8, 2019 11:04 PM
    Moderator