Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting & Copying Tables in Back-End DB (2000)

    I recently split a database into a front/back end structure (1st time I've done this). Now my VBA commands such as
    DoCmd.DeleteObject acTable, "tblName"
    end up deleting the link, not the back-end database table. How do I reference the back-end database?

    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    You can use DAO and a DDL (Data Definition Language) SQL instruction. You need to have a reference to the Microsoft DAO 3.6 Object Library for this (in Tools | References... from the Visual Basic Editor).

    Here is a procedure to use:

    Sub DeleteTableInOtherDb(strDatabase As String, strTable As String)
    Dim dbs As DAO.Database
    Dim strSQL As String
    Set dbs = OpenDatabase(strDatabase)
    strSQL = "DROP TABLE " & strTable
    dbs.Execute strSQL
    Set dbs = Nothing
    End Sub

    Call it in the form

    DeleteTableInOtherDb "ServerShareFolderMyDatabase.mdb", "tblName"

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Just a note on splitting of databases. I don't know that this is releveant to what you have done, but temporary tables that aren't being shared really don't have to be in the backend; they actually should be in the frontend so there is no problem with users stomping on each other's data.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Thanks, Mark, that helps a lot. I should move not only my temporary tables but any other table that only I need to use to the frontend.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    No - that defeats the purpose of having a back-end. Only temporary tables should be put in the front-end. Having permanent tables in the back-end means you can implement design changes to forms, reports and other objects without having to take exclusive control of the database with the tables. In any event if your database is multi-user, you want to have all of the permanent tables stored on a server so that users see the same data. We have a brief tutorial on our web-site that explores database splitting that you might find useful.
    Wendell

  6. #6
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Help! I took the advice on your web page and created 2 new databases, put all permanant tables in the backend database and all other objects in the frontend, and then created links to the tables. Now none of my vba code will work. When I click on a command button I get the message "User-defined type not defined
    *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
    * There may have been an error evaluating the function, event or macro

    I looked at the code and it all looks the same. Any advice?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting & Copying Tables in Back-End DB (2000)

    If your now-split database was originally created in Access 97 or you used DAO code, you'll need to go into the references list in the front end database and make sure you have a DAO 3.6 reference set. If you aren't using ADO code at all, you can turn off the ADO reference Access 2000 puts in by default.
    Charlotte

  8. #8
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Okay, I add the reference to DAO 3.6 and my command buttons work again! However, when I try to run a sub that worked fine before I get this error:

    Compile Error
    Method or data member not found

    and a .Edit statement is highlighted.

    I checked and the table has data to edit. What's wrong now?

    Kevin

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Perhaps this is an ADODB recordset instead of a DAO recordset - ADODB recordsets don't have an Edit method. If you post the entire procedure, we may be able to see what is happening.

  10. #10
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Okay. This sub imports data from a text file into a temporary table, manipulates the data to get the student's ID and turn the text dates into date variables, then creates proposed absences in a schedule table between the start & finish date, and then appends the original data to a permanent table.

    Private Sub cmdAbsReq_Click()

    On Error Resume Next
    DoCmd.DeleteObject acTable, "tmakAbsReq"
    DoCmd.CopyObject , "tmakAbsReq", acTable, "zztblAbsReq"

    DoCmd.TransferText acImportDelim, , "tmakAbsReq", _
    "scuhsnet.scuhs.edukevinroseinetpub_privateabsence .txt", True

    Kill "scuhsnet.scuhs.edukevinroseinetpub_privateabsence .txt"

    DoCmd.OpenQuery "qupdAbsReqInt" ' convert username to ID
    '
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tmakAbsReq")

    With rst

    intRowCnt = .RecordCount

    ReDim dateAbsBeg(intRowCnt)
    ReDim dateAbsEnd(intRowCnt)
    ReDim intIntern(intRowCnt)

    .MoveFirst

    For i = 1 To intRowCnt

    strMo = !BegMo
    intMo = MonthToInt(strMo)
    .Edit
    !BegDate = intMo & "/" & !BegNum & "/" & Year(Date)
    If !BegDate < Date - 10 Then !BegDate = DateAdd("yyyy", 1, !BegDate)
    dateAbsBeg(i) = !BegDate

    strMo = !EndMo
    intMo = MonthToInt(strMo)
    !EndDate = intMo & "/" & !EndNum & "/" & Year(Date)
    If !EndDate < Date - 10 Then !EndDate = DateAdd("yyyy", 1, !EndDate)
    dateAbsEnd(i) = !EndDate

    intIntern(i) = !Intern
    .Update

    .MoveNext
    Next i

    .Close
    End With

    Set rst = dbs.OpenRecordset("tblSchedule")

    With rst

    For i = 1 To intRowCnt

    Do While dateAbsBeg(i) <= dateAbsEnd(i)

    .AddNew
    !DateRot = dateAbsBeg(i)
    !Intern = intIntern(i)
    !Assign = "AbsProp"
    .Update

    dateAbsBeg(i) = dateAbsBeg(i) + 1
    Loop

    Next i

    .Close
    End With
    Set rst = Nothing
    Set dbs = Nothing

    DoCmd.OpenQuery "qappAbsReq"
    DoCmd.OpenForm "fpriAbsReq"

    End Sub

    Function MonthToInt(strMoConv As String) As Integer

    Select Case strMoConv

    Case "January"
    intMoConv = 1

    Case "February"
    intMoConv = 2

    Case "March"
    intMoConv = 3

    Case "April"
    intMoConv = 4

    Case "May"
    intMoConv = 5

    Case "June"
    intMoConv = 6

    Case "July"
    intMoConv = 7

    Case "August"
    intMoConv = 8

    Case "September"
    intMoConv = 9

    Case "October"
    intMoConv = 10

    Case "November"
    intMoConv = 11

    Case "December"
    intMoConv = 12

    End Select

    MonthToInt = intMoConv

    End Function

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    I don't see anything obviously wrong with your code - do you have any missing references, and do you have both ADO and DAO referenced. If the latter is the case, see if removing ADO will fix your problem, as you are using DAO commands.
    Wendell

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Wendell's tips will probably help. You can also try declaring the object variables with a DAO prefix:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    That should remove the ambiguity between ADODB and DAO too.

  13. #13
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    I don't see ADO or ADODB in my references. I only have the following:

    VBA
    MS Access 10.0 Object Library
    OLE Automation
    MS Active X Data Objects 2.5 Library
    MS Office XP Web Components
    MS DAO 3.6 Object Library

    Changing my declarations as Hans suggested seems to fix my problem. Should I go ahead and do a search & replace to change every object variable declaration?

    Kevin

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    Microsoft ActiveX Data Objects 2.5 Library is the ADO or ADODB library, you DO have a reference to it, and hence the need to prefix Recordset with DAO. You can try to clear the check box of this reference and see if the database still works.

    I recommend ALWAYS prefixing DAO and ADO objects, for it will ensure that you will always refer to the correct object types, and avoid the kind of error message you got.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting & Copying Tables in Back-End DB (2000)

    In addition to Hans' observations, if you have a reference to MS Access 10.0 Object Library then you are dealing with Access 2002 rather than 2000. That may be contributing to some of your problems.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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