Hi

I am writing a string to an access table by examining the string one character at a time and separating fields and records by ch13(return) and ch9(space) characters. Each time I get to a ch13 I write the values I have found to a table using sql. It all works lovely, but it takes a lot more time than I would like. I was wondering if there might be a quicker way.

1 thing has sprung to mind:

- If I save the string to the clipboard as text, I can paste it straight into the table in an instant and access puts the values in the right places for me automatically. I expect access is using the same method as me but is infinitely more efficient than my dodgy code.

Can I write my string to the clipboard and paste the contents into the table using code? Therefore letting access do all the formatting.

This must be simple - surely?

I've searched the net and am getting pointed in the direction of clipboard objects..

I'll be very grateful for help.

Nick

I don't have an answer for you, but am interested in doing something similar myself. One thing I've run into with a copy/paste operation in Access is that if there are multiple concurrent users on the database you are pasting to, it may only let you paste a certain number of records (200 to 500 in my case). I'm pretty sure this is an Access limitation when there are concurrent users, not a clipboard limitation. I tested the same thing on a copy of the database, and it pasted all 4000 records fine.

I started looking into this because the tables I'm working with are so large that an append operation for a mere 4000 records takes hours (I've tried). The table I'm pasting into has 1.3 million records in it (We're moving to SQL soon!)

So just keep in mind the concurrent users thing, and hopefully one of the gurus on this site will help you with the method and code.

how are you doing this? are you using a recordset? how big is the string? how many records?

You should be able to define an import specification to do this for you without any coding. Have you looked at using import?

I am not importing data from a file, it is coming in over a DDE link from an oscilloscope. I am using DDErequest to write a string to a variable, then I want to paste it into a table.

The string is about 20000 characters long including spaces and returns and is to divide into two fields with about 1700 records.

Any ideas?

Thanks

Nick

I would try saving the string to a file and using an import specification to import and parse the data into records. If that fails, then we will help you write a custom function to parse the string into your table(s).

I thought about that and will do so if neccesary. It just seems a bit untidy to drag a string in to memory then write it out to a file and drag it back in again.

I have already written a function to parse the string and insert into a table but it is quite slow. That is why I asked about controlling the clipboard.

If it is going to be a nightmare i'll just use an intermediate file.

Thanks

Nick

Whatever you prefer.

I saw your post from about a month ago, and just a few weeks ago I had to figure this out. This is what I came up with, it works really well. I've basically got a backend this is getting copied to that has a lot of concurrent users all the time, and there's around 4000 records daily that need to get imported into this table. An append is NOT an option, as it pretty much dies because of the concurrent users. It takes about 60 seconds or less with this code to get everything in there.

I've got a control on a form that this is referencing for the loop condition, that updates the count of records that have been copied (I use a count on the backup table for this) and how many are left (I use a temp table, because as you can see I'm selecting the top 250 records for the copy, and then deleting them afterwards, then looping through until they are all gone.)

I'm using queries instead of opening the tables themselves because it's much, much faster.

Tables and Queries used:
tblCopyFrom: Has an autonumber field that I can use to select TOP 250 from, in addition to the normal datafields
tblPastingTo: This is the destination table
tblPastedBackup: This is a backup table of what was copied

qryCopyFrom: "SELECT TOP 250 tblCopyFrom.RecNum, * FROM 250 tblCopyFrom;"
qryPastingTo: "SELECT * FROM tblPastingTo WHERE somefield is null;" --I choose a field that is never null, so that the query returns no rows. This way it pops up very quickly, and allows me to pastappend quickly
qryPastedBackup: "SELECT * FROM tblPastedBackup WHERE somefield is null;"