Results 1 to 5 of 5

Thread: Compile error

  1. #1
    Join Date
    Oct 2005
    Posts
    23

    Unanswered: Compile error

    n my function listed below i get compile error in the following line :
    adb.OpenCurrentDatabase (strDbName,,strPassword)
    can you help ?

    Private Function KillObject(strDbName As String, acObjectType As Long, strObjectName As String, StrPassword As String)
    'Call KillObject(GPath, 0, "products")
    Dim StrPassword As String
    StrPassword = "secret"
    Dim db As DAO.Database
    Dim adb As Object
    Set adb = CreateObject("Access.Application")
    adb.OpenCurrentDatabase (strDbName,,strPassword)
    adb.DoCmd.DeleteObject acObjectType, strObjectName
    adb.CloseCurrentDatabase
    Set adb = Nothing
    End Function

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try:
    Code:
    adb.OpenCurrentDatabase strDbName, , strPassword
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Private Function KillObject(strDbName As String, acObjectType As Long, strObjectName As String, StrPassword As String)...
    'Call KillObject(GPath, 0, "products")
    Dim StrPassword As String
    StrPassword = "secret"
    Dim db As DAO.Database
    Dim adb As Object
    Set adb = CreateObject("Access.Application")
    adb.OpenCurrentDatabase (strDbName,,strPassword)
    adb.DoCmd.DeleteObject acObjectType, strObjectName
    adb.CloseCurrentDatabase
    Set adb = Nothing
    End Function
    If you copied and pasted this code directly from the module, then you're passing in StrPassword and trying to open with strPassword. Capital vs Lowercase - two different variables.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jmahaffie
    If you copied and pasted this code directly from the module, then you're passing in StrPassword and trying to open with strPassword. Capital vs Lowercase - two different variables.

    I'd be very surprised if Access VBA cared about CaPiTaLiSaTiOn

    but I wouldn't mind betting its whinging about the nature of the object and what you are trying to do
    looking at the help files it suggests that you should open an object of type access.application, then the rest of the porcess should go OK

    ...from the help files bundled as part of Access.....
    Code:
    ' Include the following in Declarations section of module.
    Dim appAccess As Access.Application
    
    Sub DisplayForm()
        ' Initialize string to database path.
        Const strConPathToSamples = "C:\Program " _
            & "Files\Microsoft Office\Office\Samples\"
    
        strDB = strConPathToSamples & "Northwind.mdb"
        ' Create new instance of Microsoft Access.
        Set appAccess = _
            CreateObject("Access.Application")
        ' Open database in Microsoft Access window.
        appAccess.OpenCurrentDatabase strConPathToSamples
        ' Open Orders form.
        appAccess.DoCmd.OpenForm "Orders"
    End Sub
    Last edited by healdem; 11-14-07 at 09:09.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    If you copied and pasted this code directly from the module, then you're passing in StrPassword and trying to open with strPassword. Capital vs Lowercase - two different variables.
    With some languages (long ago) this could be a determining factor although not the ultimate one. I wouldn't be surprised if some IDE's out there don't autocorrect variable structure as most do today.

    In the Micrsoft Access VBA IDE a variable that is placed within a Declaration....is, so to speak, the winner. Once a variable is Declared either Privately, Publicly, or Globaly, letter case never become a factor since the IDE will automatically make the correction unless of course the variable is spelled incorrectly. The same holds true with Control names etc. Once placed within the Properties box, it is declared as such. I personaly prefer to place some capitalization within my Variables, Constants, Procedures, and Object names for clarification but I never type them the way I declare them. There's to much 'Shift Key' work for me if you know what I mean. I let the IDE take care of that business. As a matter of fact....as I'm typing along and I don't see the capitalization happing within my variable or object names then I instantly know that I mispelled it and make the necessary corrections right away. I am after all....a crappy speler.

    In your particular case, I think the Compiler is a wee bit confused because it it doesn't know how to point you to the lesser of two evils so, it just highlights a line and blurts out a line that indicates something like "Hey...this won't Work". In other words, because adb.OpenCurrentDatabase (strDbName,,strPassword) is highlighted as being the Compile error doesn't necessarily mean this is where the error is really situated.

    Look a little further up for what I see confuses me as it would the Compiler. Why is StrPassword declared twice?

    Since you have already declared StrPassword As String within you Function declaration:

    Code:
    Private Function KillObject(strDbName As String, acObjectType As Long, _
                                       strObjectName As String, StrPassword As String)
    why would you want to add the additional code to do it yet again.

    Code:
     Dim StrPassword As String
    StrPassword = "secret"
    Why not just pass: "secret" within your function Call sine it is after all designed to do that:

    Code:
    Call KillObject("C:\MyWorkDBFolder\MyDatabaseName.mdb", acTable, "Products","Secret")
    As you may have guessed, the acTable parameter will denote a table as the ObjectType. For a Form you would use acForm. Check the Object browser for more...

    What I find amusing is that above the second declaration of the StrPassword (Dim StrPassword As String) is a commented line which displays how to use the KillObject function yet it shows no requirement for the fourth and last parameter, StrPassword. Something must be passed here since this parameter is not declared as Optional. So for accuracy the Comment line should read:

    ' Call KillObject(GPath, 0, "Products", "password")

    or at very least:

    ' Call KillObject(GPath, 0, "Products", "")

    Looking at the code, my uneducated guess would be that you are trying to delete a table.....in another Database. Is this the case or are you trying to Delete a table in the Database that makes the function call?

    If you are trying to delete a Table in your current database then you could save a little typing and try this:

    DoCmd.CurrentDb.Execute "DROP TABLE [myTableName];"

    OR just...

    DoCmd.DeleteObject acTable, "[myTableName]"

    then refresh the Table Definitions Table with:

    Application.CurrentDb.TableDefs.Refresh

    If you are deleting a table in another Database then try this:

    Code:
    Dim strDbName As String
    strDbName = thePathAndFileNameOfDatabaseInQuestion
    CurrentDb.Execute "DROP TABLE [;DATABASE=" & strDbName & ";PWD='secret'].[theTableName]"
    Lot less code (in my opinion)

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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