Adding/appending Query 1

[Herriot]

Aim: A tab delimited text file

Current procedure: A make-table query that extracts the required data (based on a prompt)to a table. The table contents is then exported to a TAB delimited file. This file is then uploaded to a different database via the web.

Problem: Destination DB requires that, if present, fields follow a particular format. Because I am using a make-table the field lengths are always reset to the default (255 in a normal text field)

Possible Solutions:

1 - Change formatting of newly created table to fit the destination DB. This is time consuming and also has another problem that I can't seem to rid of (with regard to DOB see below).

2 - Change Query to an Append Query. Which does do the trick in that the formatting of the table is preserved. But I would want to clear out any existing data. This - I think - would need a MACRO or some VB scripting to clear out the data the append the results of the query. The problem with the DOB still exists (see below)

Anyone got any ideas on the best way to acheive this Macro and/or VB and any further help would be gratefully received.

The DOB problem
The DOB field is always exported in this format: dd/yy/mmmm 00:00:00 and we need just the dd/yy/mmmm (european-UK)format. Despite the format being set to shortdate and the following added to the query Left$([dbo_a_stu]![stu_dob],10)

 

[scriverb]

If you would select option #2 and append records to an existing table you would only have to execute a Delete query first to delete all records. SQL for that query is as follows:
DELETE *
FROM TableName;

The procedure would be to execute this query first through code and then execute your Append query to refill the records for the next exporting process.
DoCmd.SetWarnings false
DoCmd.OpenQuery "qryDeleteQuery"
DoCmd.OpenQuery "qryAppendQuery"
DoCmd.SetWarnings true

As far as the DOB format try using this:
DOBUK: CStr(Format([TableName]![DOB],"dd/yy/mmmm&quot)
This converts it to your format as requested and then converts it to a string representation.

Hope this helps.
Bob Scriver

 

[Herriot]

Bob

Thanks a lot for your help. I decided on a delete query as you suggested (why I didn't think of that I'll never know) then the append one. I automated it all - even to exprting the data to a TAB delimited text file - using a macro.

The DOB problem is also solved thanks to your tip. BTW the format I put down was dd/yy/mmmm and of course I meant dd/mm/yyyy.

I sometimes feel I put too much thinking into the problem and at times can't see the wood for the trees.

Thanks again for your help

 

[scriverb]

Hey we all do that. I looked that format over and I said well those guys in the UK are strange at times so if he wants it that we so be it. No not really. I knew you would change it if you needed to.

Good luck. Bob Scriver