Solved

expert table to csv in access

Posted on 2014-10-08
2
188 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
Comment Utility
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
Comment Utility
worked a treat thank you
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 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

11 Experts available now in Live!

Get 1:1 Help Now