Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

expert table to csv in access

Posted on 2014-10-08
2
Medium Priority
?
200 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

876 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