Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Database Path (97 SR2)

    When I execute the following code the temporary querydef statement (qdftemp.execute) looks for the database in the default database folder defined in Tools, Options, General. This of course is the wrong location. I get an error message number of 3024. In the Debug window, dbs is correctly defined in Name with the correct path.
    I have compacted and repaired the database with no change in outcome. The query works quite well in the SQL window.

    Sub findname()

    Dim dbs As Database
    Dim strbasename, strsysname, strcomparename As Variant
    Dim strtransfer As String
    Dim qdftemp As QueryDef

    Set dbs = CurrentDb


    strtransfer = "INSERT INTO dbs.datascreen ( 3HSystemName, 3HPWSID, DNRReFNo ) " & _
    "SELECT [3H ALL].SYSTEM_NAM, [3H ALL].PWSID, [3H ALL].REGNO " & _
    "FROM [3H ALL] WHERE ([3H ALL].SYSTEM_NAM Like ""*JOE*"");"
    'MsgBox strtransfer
    Set qdftemp = dbs.CreateQueryDef("", strtransfer)
    qdftemp.Execute


    Set dbs = Nothing
    End Sub

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

    Re: Database Path (97 SR2)

    1. You can't use dbs within the SQL string. It is interpreted as a literal string. And I don't think you need it there anyway; since dbs refers to the current database, you don't need to specify where the datascreen table lives.
    2. Is there a specific reason for using a temporary querydef? You can execute an SQL statement without creating a querydef. Try this:

    strTransfer = "INSERT INTO datascreen ( 3HSystemName, 3HPWSID, DNRReFNo ) " & _
    "SELECT [3H ALL].SYSTEM_NAM, [3H ALL].PWSID, [3H ALL].REGNO " & _
    "FROM [3H ALL] WHERE ([3H ALL].SYSTEM_NAM Like ""*JOE*"");"

    dbs.Execute strTransfer

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Database Path (97 SR2)

    Thanks Hans, both worked fine.

    I hadn't done anything with temp querydefs and thought they might apply while I was cleaning up data. I'm mining a few million records of string data and find queries using Like are much faster than iterating through using code and recordsets.

Posting Permissions

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