asked on

Use VBS to Export Access Query to Tab-Delimited Text

I'd like to schedule a task to export a tab-delimited file based on an Access query. For several reasons, I'd like to do this by running a Visual Basic script. I have the code below, which is producing an error on Line 19 Char 1 ("a = rs.GetString"): Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Can you help? Here is the code:

db = "path to database"
TextExportFile = "path to text file"

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

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

strSQL = "SELECT * FROM QueryName"

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

Jeffrey Coachman

<Requested operation requires a current record.>
typically means that no records exist

Your coding seems a bit "loose" here
Can you post the *entire* code please...
I don't see how/where you are declaring your variables...

Can you state what this code is supposed to be doing, ...perhaps there is a simpler method...

davewalter1

ASKER

Hi, thanks for the response.

I actually borrowed this code (I'm not a real programmer -- I muddle through), and that's all there is of it. All I need to do is run a query, pull all the records, and write them out to a tab-delimited text file.

I've been having problems with Access saving export specifications, or I would just use the wizard, save the specs, call them from a macro, and be good to go. But I can't get it to save export specs.

So for this and other reasons, I thought this would be a simple way to go. I'm not committed to this code, however, so if you know a better approach, I'm all ears!

Thanks again.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davewalter1

ASKER

Thanks for your suggestions, but I've gone a different route. I finally got the Access extraction process to work well enough to get a macro to work, and I'm running that macro as a scheduled task.

Capricorn, your code returned the error message box, but I suspect that's my fault because of the paths I was using to get to the database. So I'm trusting that your solution would have worked had I done my part, and I'm awarding you the points.

Thanks again for your trouble!