Web Development
Using a DAO recordset, I am looking for a good method to copy one or more records to the clip board in tab delimted format, with column headers, and, most important, copy records from the clipboard back to a recordset object.
I am giving additionalpoints because I am looking for code to help me out.
Here's a link:
How to Retrieve Information from the Clipboard
http://www.microsoft.com/isapi/gosupport.asp?TARGET=/support/KB/Articles/Q138/9/10.asp
(no space in url)
Advanced: Requires expert coding, interoperability, and multiuser skills.
Microsoft Access does not have a command to retrieve information from the Clipboard. To retrieve information from the Clipboard, you need to define a Visual Basic for Applications function that calls several Windows API functions. This article defines a function that retrieves text from the Clipboard.
How to Send Information to the Clipboard
http://www.microsoft.com/isapi/gosupport.asp?TARGET=/support/KB/Articles/Q138/9/09.asp
(no space in url)
Advanced: Requires expert coding, interoperability, and multiuser skills.
Microsoft Access does not have a command that sends information to the Clipboard. To post information to the Clipboard, you need to define a Visual Basic for Applications function that calls several Windows API functions. This article defines a function that copies text to the Clipboard.
note:
If you notice in the articles, you send items and retrieve items in the clipboard in a string format. The best way to implement this is to use XML.
In access 2002, there are two XML support functions, ExportXML and Import XML.
You export the recordset into an XML file, you open the file and read the file into an XML string, send it to clipboard using the function described in the abovementioned article.
You import from the clipboard (using the function described in the abovementioned article), parse the string (this is where the XML comes in, if it is in XML format, you can convert it directly to recordset). if the string is not in the XML format, you have to write a parser function that parses the string and puts in in the proper recordset format (create a table, addnew items from the string one by one according to delimiter)
here is the link about the ImportXML and ExportXML
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart02/html/sa02a1.asp (no space in url)
hope this helps.
BTW:
if you really need to enter the data in a tab delimited format, try this pseudo code:
dim mystring as string
'get field names from dao
mystring = mystring & rs.field(1).name & vbtab & rs.field(2).name & vbcrlf
do until rs.eof
mystring = mystring & rs!field1 & vbtab & rs!field2& vbcrlf
rs.movenext
loop
ClipBoard_SetData(mystring)
To get data from clipboard try this pseudo code:
dim mystring as string
mystring = ClipBoard_GetData()
parse(mystring)
sub parse(byval mystring)
dim firstloc, secondloc, vbcrlfloc as integer
'no table exist yet
'first line of string contains headings
firstloc = 1
secondloc = instr(1, mystring, vbtab)
vbcrlfloc = instr(firstloc, mystring, vbcrlf)
do until vbcrlfloc < secondloc
fieldname[index] = mid(mystring, firstloc, secondloc - firstloc)
index = index + 1
firstloc = secondloc
secondloc = instr(1, mystring, vbtab)
loop
'todo:
create table using fieldname[index]
open table using rs
'populate table
do until secondloc = 0 'not found; end of string
rs.addnew
firstloc = secondloc
do until vbcrlfloc < secondloc
secondloc = instr(1, mystring, vbtab)
vbcrlfloc = instr(firstloc, mystring, vbcrlf)
rs.field(index) = mid(mystring, firstloc, secondloc - firstloc)
firstloc = secondloc
loop
rs.update
loop
end sub
the pseudo code is somewhat crude, but i hope you get the picture :)
hope this helps.
by c1980
by c1980
Thanks for the response!
er, I forgot to say that I am using VB6 and not ACCESS. There is already a function there to put and get data from the clipboard. That's not the problem.
Oh, well, I guess I will have to go to the lengthy process of looping through the clipboard text. I only thought someone would have an easier way.
by sl-campbell
Under DAO there is a little more coding involved.
You need to loop through the selected records (the selected record in a DBGrid is the same record in the underlying recordset) and fields and add their values to a string with the desired delimiter,and, when looping to the next record, add a vbCrLf to the string. You could also use the GetRows and loop thru the Array.
Reading the records from the clipboard using the clipboard object is just as easy. You can create an array from the string using the Split method and using the vbCrLf as a delimiter. Then each element of the array is a record. Then, when looping through the array, you could then use a second array and the split function to put the fields into the array. Then taking that array, you assign the element values to the field values. You need to capture errors returned in case the field type isn't the same.
You could also copy the clipboard contents to a text file, then using DAO and the Text ISAM driver, use
INSERT INTOmyTable SELECT * FROM [the TextFile]
Jet will then copy all the reords in. Use a Schema.Ini for this.
The same could be done the otherway around, copying the records from the table and inserting them into a temp text file, with delimiters. Then, read the complete file into one string and paste it into the clipboard so other applications can still use the data.
Under ADO this is so, so much easier. You can copy the data to the clipboard with just a few simple lines of data and then paste the data back into any desired recordset object.
by c1980
This is probably the best answer under DAO.
I may post another similar question, but using ADO. If you post a workable answer, you'll get all of the points!
by c1980
This discussion is closed to new replies.
Sort by Trending