CODEWITHSUNDEEP
ms-accessvbscript
Joshxtothe4
Joshxtothe4

Reputation: 4203

vbscript to export an access query to a tab delimited file not working

I have this code:

db = "C:\Dokumente und Einstellungen\hom\Anwendungsdaten\BayWotch4\Neuer Ordner\baywotch.db5"
TextExportFile = "C:\Dokumente und Einstellungen\hom\Anwendungsdaten\BayWotch4\Neuer Ordner\Exp.txt"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source =" & db

strSQL = "SELECT * FROM tblAuction1"

rs.Open strSQL, cn, 3, 3

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.CreateTextFile(TextExportFile, True)

a = rs.GetString

f.WriteLine a

f.Close

Which is meant to connect to an access database and produce a tab delimited text file. tblAuction1 is a query in the database, and definitly exists and is not misspelt in any way, but I get an error that it cannot be found or does not exist. When I change it to tblAuction which is the name of the table, I get an error stating f.WriteLine a has been called incorrectly.

edit: I now only get a problem with f.writeline a, saying an incorrect argument has been supplied. I no longer have a problem with tblAuction1

edit: the sql code used for my query:

SELECT tblAuction.article_no, tblAuction.article_name, tblAuction.subtitle, tblAuction.current_bid, tblAuction.start_price, tblAuction.bid_count, tblAuction.quant_total, tblAuction.quant_sold, tblAuction.start, tblAuction.ends, tblAuction.origin_end, tblUser.user_name, tblAuction.best_bidder_id, tblAuction.finished, tblAuction.watch, tblAuction.buyitnow_price, tblAuction.pic_url, tblAuction.private_auction, tblAuction.auction_type, tblAuction.insert_date, tblAuction.update_date, tblAuction.cat_1_id, tblAuction.cat_2_id, tblAuction.article_desc, tblAuction.countrycode, tblAuction.location, tblAuction.condition, tblAuction.revised, tblAuction.paypal_accept, tblAuction.pre_terminated, tblAuction.shipping_to, tblAuction.fee_insertion, tblAuction.fee_final, tblAuction.fee_listing, tblAuction.pic_xxl, tblAuction.pic_diashow, tblAuction.pic_count, tblAuction.item_site_id
FROM tblUser INNER JOIN tblAuction ON tblUser.id = tblAuction.seller_id;

Upvotes: 2

Views: 5285

Answers (3)

KeesDijk
KeesDijk

Reputation: 2329

I have tried to reproduce this on several databases and machines, I can't get your code to fail.

Leaves :

  • a corrupt database, could you please run repair and try again ?
  • Fields in your database that are throwing of the query, I have tried several possibilities but can't find anything that brakes your code. To exclude other things you could try to create a new table and see if your code works on that table.
  • something wrong with your dll's , could you try it on another machine.

Answer (to see how we came to the answer see the comments)

There are unicode characters in your database that writeline does not accept because you created the textfile as ASCI.The characters in this case specifically where ♥♥♥

To make it work:

Set f = fs.CreateTextFile(TextExportFile, True, True)

P.S.

This question was answered earlier using the transfertext macro here As Remou points out this looks like a cleaner solution. To make it work with non-default delimiters is a bit of a pain. First start exporting the query you like to export by right clicking and choose export. In the following dialogs specify the specifications and save these. When creating the macro select the specifications you just saved.

Upvotes: 2

Tester101
Tester101

Reputation: 8182

Try this


cn.Open _
  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Persist Security Info=False;" & _
  "Data Source=" & db

Upvotes: 0

Daniel Kreiseder
Daniel Kreiseder

Reputation: 12395

I think there is something wrong with the spaces in your connection string

Try this:

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = db
cn.Open

HTH

Update: Maybe there is a problem with the access rights to the database? Or the mdb is already opened exclusively by another user (You with your access in design mode)?

Upvotes: 0

Related Questions