expert table to csv in access

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

PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
worked a treat thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.