Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Error Select *.* From Pass Through Query into Table

    Hi all,

    Using Access 2007,

    I am trying to get some data into tmp tables from pass through queries so I can transfer out to text file

    I keep receiving error:
    Run-time error '3070':

    The Microsoft Access database engine does not recognize '*.*' as a valid field name or expression
    Any idea what I'm doing wrong here?

    thx
    w


    Code:
    Option Compare Database
    
    Sub ExportNFLTables()
     '
        'Purpose: Export main NFL tables to csv files before beginning maintenance
        '
        '
        '
        'Date       Developer       Action                      Comments
        '---------------------------------------------------------------------------------
        '04/17/12   ws              Created
        
        'Initialize
         Dim strDate As String
         Dim strTime As String
        
         strFilePath = "c:\archive"
         strDate = Format(Now(), "YYYY.MM.DD")
         strTime = Format(Now(), "HH.MM.SS")
         
        'Drop tmp table to receive results of pass through query
         On Error Resume Next
         CurrentDb.Execute "DROP table xtmp1"
         On Error GoTo 0
         
        'Jam the results of the pass through query into the tmp table
         CurrentDb.Execute "SELECT *.* INTO xtmp1 FROM tblAFC"
         
        'Drop tmp table to receive results of pass through query
         On Error Resume Next
         CurrentDb.Execute "DROP table xtmp2"
         On Error GoTo 0
         
        'Jam the results of the pass through query into the tmp table
         CurrentDb.Execute "SELECT *.* INTO xtmp2 FROM tblNFC"
    
        'Export Table 1
         DoCmd.TransferText _
            transferType:=acExportDelim, _
            Tablename:="xtmp1", _
            FileName:=strFilePath & AFC & "_" & strDate & "_" & strTime & ".csv", _
            HasFieldNames:=True
            
        'Export Table 2
         DoCmd.TransferText _
            transferType:=acExportDelim, _
            Tablename:="xtmp2", _
            FileName:=strFilePath & NFC & "_" & strDate & "_" & strTime & ".csv", _
            HasFieldNames:=True 
    
        'Tidy up
         On Error Resume Next
         CurrentDb.Execute "DROP table xtmp1"
         CurrentDb.Execute "DROP table xtmp2"
         On Error GoTo 0
    End Sub

  2. #2
    Join Date
    Apr 2012
    Posts
    28
    Try

    "SELECT * INTO xtmp1 FROM tblAFC"

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Ieuan,

    That did it.
    The csv files are being created, but the "." in the Date and Time String is replaced with "#"

    I would like this
    tblNFC_2012.04.18_09.12.15.csv

    But I am getting this
    tblNFC_2012#04#18_09#12#15.csv

    thx
    w

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use an explicit format for ther date and time
    eg
    dim filesuffix as string
    dim MyFilename as string

    filesuffix = format( now(), "YYYYNNDDHHMMSS"
    myfilename = tblNFC & filesuffix & ".CSV"
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks healdem,

    I declared as
    Code:
    strDate = Format(Now(), "YYYY.MM.DD")
    strTime = Format(Now(), "HH.MM.SS")
    And used as
    Code:
    FileName:=strFilePath & tblAFC & "_" & strDate & "_" & strTime & ".csv"
    It appears that Access VBA does not like the "." as a delimiter and replaced with the "#"

    The code works well as written in Excel VBA, use it all the time

    thx
    w
    Last edited by goss; 04-18-12 at 13:42. Reason: typos

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by goss View Post
    Thanks healdem,

    I declared as
    Code:
    strDate = Format(Now(), "YYYY.MM.DD")
    strTime = Format(Now(), "HH.MM.SS")
    And used as
    Code:
    FileName:=strFilePath & tblAFC & "_" & strDate & "_" & strTime & ".csv"
    It appears that Access VBA does not like the "." as a delimiter and replaced with the "#"

    The code works well as written in Excel VBA, use it all the time

    thx
    w
    its far more likely the OS doesn't like the full stop in a file name, or it coudl be that Acces isn't fully windows aware

    to be honest you don't need the delimiter, its just additional characters that convey no added value

    I suspect you mean
    FileName:=strFilePath & "tblAFC_" & strDate & "_" & strTime & ".csv"
    which functionally is no diferent to
    FileName:=strFilePath & "tblAFC_" & format(now(),"YYYYMMDD_HHMMSS) & ".csv"
    an advantage of assigning the timestamp to a variable is that all your exports can have the same suffix which can make identifying file sets easier, especially if you have to show such files sets to an outside authority
    Last edited by healdem; 04-18-12 at 14:21.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks healdem,

    It is strictly a formatting preference, I think it is easier to read with the delimeter than all jammed together.

    Still, I prefer the YYYYMMDD over the YYYY#MM#DD I am currently getting, so I may have to go with it.

    Seems a little odd that
    Code:
    strDate = Format(Now(), "YYYY.MM.DD")
    strTime = Format(Now(), "HH.MM.SS")
    works with Excel VBA and not Access VBA

    thx
    w

Posting Permissions

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