Solved

expert table to csv in access

Posted on 2014-10-08
2
190 Views
Last Modified: 2014-10-08
I want to expert some tables I have a recordset that moves through the tables i want to save, I am having trouble
saving the table

 it says error 3027

Dim Db As DAO.Database
Set Db = CurrentDb()
Dim rstClientDecodeTables As DAO.Recordset
Dim rstTable As DAO.Recordset
Dim rstTable2 As Object
Dim fldNew As Object
Dim strSQL As String
Dim strSQL2 As String
Dim strfield As String
Dim TableNameinFull As String
Dim qdef As QueryDef

Dim WordCountDecode As Long
Dim StrWordLength As String

Dim ModelName As String
Dim TblLength As Long
Dim ClientLength As Long

Dim reccount As Long
reccount = 0
strSQL = "SELECT MSysObjects.Name AS table_name" _
& " FROM MSysObjects" _
& " WHERE (((MSysObjects.Name) Like " & """*Extractionswithrules*""" & "))" _
& " ORDER BY MSysObjects.Name;"

Set rstClientDecodeTables = Db.OpenRecordset(strSQL)

Debug.Print strSQL

With rstClientDecodeTables
     If rstClientDecodeTables.BOF And rstClientDecodeTables.EOF Then
     
     Else
    .MoveFirst

    Do Until rstClientDecodeTables.EOF
    
        fname = "n:/temp/pete/" + Format(Date, "yyyymm") + " " & .Fields("table_name").Value.csv
        DoCmd.TransferText acExportDelim, , .Fields("table_name").Value, .Fields("table_name").Value, True
        .MoveNext
    Loop
End If
End With

rstClientDecodeTables.Close
Set rstClientDecodeTables = Nothing

Open in new window

0
Comment
Question by:PeterBaileyUk
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40368063
I think you're missing some punctuation:

fname = "n:/temp/pete/" + Format(Date, "yyyymm") + " " & .Fields("table_name").Value.csv

Should be:

fname = "n:/temp/pete/" + Format(Date, "yyyymm") + " " & .Fields("table_name").Value & ".csv"

And your TransferText doesn't seem to have the right value for the File argument. I think it should be:

DoCmd.TransferText acExportDelim, , .Fields("table_name").Value, fName, True
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 40368068
worked a treat thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now