Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Location
    India
    Posts
    42

    Talking Unanswered: Create new Backend for new fiscal year

    Hi all,

    I need an idea to create BE of each fiscal (Mar to Apr) year through FE.

    So, what I think to do so, I need a form on FE and a command button and set a code to create new database and export some tables with all data and some without data from my existing BE (Data2011_2012.mdb) to new BE and name or rename new BE as Fiscal year (Data2012_2013.mdb).

    Please guide me to do so. Any help & suggestion will be appreciated.

    Thanks & Regards,
    Anuj

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No
    defoo NOT a smart call
    redesign your existing application & tables so you can identify what fiscal year which data belongs to. Quite often this is no more than a table identifying what months comprise what periods of what financial year. the front end however may need more detailed rethinking

    at some stage some bright spark* will request that you do year on year comparatives or other forms of comparative analysis


    *it doesn't have to be 'a bright spark' that requests this, it could well be an accountant
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Location
    India
    Posts
    42
    Hi Healdem,

    I think you took it wrong. I'm not thinking about fiscal year issue. I need to know about following ideas and code:

    1-Create BE from FE.
    2-Export tables from existing BE to new BE.
    3-Export some tables with data.
    4-Export some tables without data (structure only).
    5-Rename new BE with any prefixed format like (Data2.mdb)

    I hope you'll understand my expectation.

    Thanks,
    Anuj

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    I understand your expectation
    and its a bad idea

    however if you 'MUST' proceed with that then it can be done
    whether you do its as a purely VBA process or a mix of VBA and VBS/operating system commands is up to you.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Location
    India
    Posts
    42
    Thanks healdem,

    But I'm trying the following code to do so but got an error "Wrong number of argument or invalid property" in bold line, please help me :

    Option Compare Database
    Option Explicit

    Public Function CopyDatabase()
    On Error Resume Next
    Dim Response As VbMsgBoxResult
    Dim strFullDBName As String
    Dim strFullDBNameNew As String

    strFullDBName = DLookup("Database", "MSysObjects", "[flags] = 2097152") 'For linked table location
    strFullDBNameNew = Left(strFullDBName, Len(strFullDBName) - 4) & "_New.mdb"

    Application.Screen.MousePointer = 11
    DBEngine.CreateDatabase strFullDBName, strFullDBNameNew, , , ";pwd=zujan"
    With New FileSystemObject
    DoCmd.SetWarnings False
    DoCmd****nSQL "DELETE * FROM tblChallan"
    DoCmd****nSQL "DELETE * FROM tblCustomerSale"
    DoCmd****nSQL "DELETE * FROM tblEstimateSpare"
    DoCmd****nSQL "DELETE * FROM tblEstimateVehicle"
    DoCmd****nSQL "DELETE * FROM tblFinance"
    DoCmd****nSQL "DELETE * FROM tblFinanceDetail"
    DoCmd****nSQL "DELETE * FROM tblInvDetail"
    DoCmd****nSQL "DELETE * FROM tblInvoicentry"
    DoCmd****nSQL "DELETE * FROM tblMM"
    DoCmd****nSQL "DELETE * FROM tblRecieptDisburse"
    DoCmd****nSQL "DELETE * FROM tblSerJobCard"
    DoCmd****nSQL "DELETE * FROM tblSpareSlInv"
    DoCmd****nSQL "DELETE * FROM tblSparesPr"
    DoCmd****nSQL "DELETE * FROM tblSparesPrInv"
    DoCmd****nSQL "DELETE * FROM tblSparesSl"
    DoCmd.SetWarnings True
    MsgBox ("Database successfully created."), vbInformation, "Database"
    End With
    Exit_CopyDatabase:
    Application.Screen.MousePointer = 0

    Exit Function

    Thanks,
    Anuj

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    CreateDatabase Method tells you that the 'Connect' parameter is required.

    you will need to specify the locale,
    you may need to specify the JET version
    and you have the option of specifying it as encrypted
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2012
    Location
    India
    Posts
    42
    I am trying the following code but getting an error in the below line "Microsoft Office Access cannot open the database because it is missing or opened exclusively by another user " in the following line:

    blnCopySuccessful = Application.CompactRepair(strCurrentBackend, strNewBackend, False)

    Whereas, BE was not opened during running the code process at all but BE is password protected.

    Actual Code:
    ---------------------------
    Option Compare Database
    Option Explicit

    Public Function CopyBackend()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strCurrentBackend As String
    Dim strNewBackend As String
    Dim blnCopySuccessful As Boolean

    For Each tdf In CurrentDb.TableDefs
    If Len(tdf.Connect) > 0 Then
    ' get current path for backend
    strCurrentBackend = Mid(tdf.Connect, 11)
    ' build path for new backend
    strNewBackend = _
    Left(strCurrentBackend, InStrRev(strCurrentBackend, ".") - 1) & _
    "_New" & Mid(strCurrentBackend, InStrRev(strCurrentBackend, "."))
    Exit For
    End If
    Next tdf

    ' create new back end
    blnCopySuccessful = Application.CompactRepair(strCurrentBackend, strNewBackend, False)

    If blnCopySuccessful Then
    ' return refernce to new backend
    Set dbs = OpenDatabase(strNewBackend)
    ' empty relevant tables in new backend
    With dbs
    .Execute "DELETE * FROM tblChallan", dbFailOnError
    .Execute "DELETE * FROM tblCustomerSale", dbFailOnError
    .Execute "DELETE * FROM tblEstimateSpare", dbFailOnError
    .Execute "DELETE * FROM tblEstimateVehicle", dbFailOnError
    .Execute "DELETE * FROM tblFinance", dbFailOnError
    .Execute "DELETE * FROM tblFinanceDetail", dbFailOnError
    .Execute "DELETE * FROM tblInvDetail", dbFailOnError
    .Execute "DELETE * FROM tblInvoicentry", dbFailOnError
    .Execute "DELETE * FROM tblMM", dbFailOnError
    .Execute "DELETE * FROM tblRecieptDisburse", dbFailOnError
    .Execute "DELETE * FROM tblSerJobCard", dbFailOnError
    .Execute "DELETE * FROM tblSpareSlInv", dbFailOnError
    .Execute "DELETE * FROM tblSparesPr", dbFailOnError
    .Execute "DELETE * FROM tblSparesPrInv", dbFailOnError
    .Execute "DELETE * FROM tblSparesSl", dbFailOnError
    End With

    dbs.Close
    Set dbs = Nothing

    ' change Connect property of each table in front end
    ' and refresh links
    For Each tdf In CurrentDb.TableDefs
    If Len(tdf.Connect) > 0 Then
    tdf.Connect = Replace(tdf.Connect, strCurrentBackend, strNewBackend)
    tdf.RefreshLink
    End If
    Next tdf
    MsgBox "New database has been created and connected successfully.", vbInformation, "Done"
    Else
    MsgBox "Unexpected error occured during database creation.", vbExclamation, "Warning"
    End If

    End Function

Posting Permissions

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