VB6 access recordset

[abaldwin]

Have a vb6 application with an Access backend db (used only for storage).

Would like to do the following:
1. Export certain records from 12 tables into XML (have done this fine with ado.save <filename> adpersistxml).

These files would then be sent via email to some remote users who would need to then...

2. Using the application - Import the data from the 12 files into their local copy of the database.

Number 2 is where I would like some help and possibly some examples.

The databases on both ends are identical would just like the simplest way to write the VB6 code to do the import.

Thanks

Andy Baldwin


Andy Baldwin

 

[ChewDoggie]

Do you want the VB application to send the email? Or would the file be saved by the app and sent via email manually?

AMA

 

[abaldwin]

The XML files are generated using rs.save adpersistxml method during the "export" process. Basically a query is run to gather the information from each of the tables and each record(s) pertaining to the job are written in this way to the XML file.

I know that I can read connect a recordset to the xml file and using another connection (to the local db), I can create a sql statement that looks something like

"INSERT INTO <table> (field1, field2 etc etc) VALUES (rs1!fieldname, etc etc) I just think that since I have the xml file and can connect that there must be a better way to construct the INSERT statement. Actually trying to see if there is a way to insert the recordset into the db without having to write so many insert statments and then executing them.

Andy

Andy Baldwin

 

[ChewDoggie]

I asked b/c I'm doing something similar where I query some tables, create some recordsets (rs) and convert the rs to XML and pass it to a server via a stream object, and then deconstruct the xml on the server side and insert the records into the appropriate tables.

Sound like what you'rre doing?

AMA

 

[abaldwin]

Basically yes, I create some recordsets adn save each one to its own XML file using the above ado example.

The files are then emailed to another user who will then need to import the records in the XML files into their local copy of the db.

I just do not want to write 12 very long SQL insert statements (may be more in the future) and secondly the end users need simply an "Import" button to do this since they do not have the experience or the knowledge to process manually.

Andy

Andy Baldwin

 

[ChewDoggie]

I'm assuming you have a winsock control that appears on your form, set the "host" and "port number" properties.

and you should have some code on the form that looks like this:

Dim objStream As ADODB.Stream
Set objStream = New ADODB.Stream
sRs.Save objStream, adPersistXML
str = objStream.ReadText(adReadAll)

' set the winsocket properties
Winsock1.protocol = sckTCPProtocol
Winsock1.Connect

Do Until Winsock1.State = 7
DoEvents
Loop

Winsock1.SendData str

On the server side, you'll create an application that'll point to that port and "listen" for any requests that come in.

My code is extensive but here's the jist of it:

sub form_load()
Socket(0).LocalPort = YourLocalPortNumber
Socket(0).Listen
end sub

Private Sub Socket_ConnectionRequest(Index As Integer, ByVal requestID As Long)
If Index = 0 Then
sRequestID = requestID
iSockets = iSockets + 1
Load Socket(iSockets)
Socket(iSockets).LocalPort = YourLocalPortNumber
Socket(iSockets).Accept requestID
End If
End Sub

Private Sub Socket_DataArrival(Index As Integer, ByVal bytesTotal As Long)
Dim sItemData As String
Dim strData As String
Dim strOutData As String

Socket(Index).GetData sItemData, vbString
' search response data there and create output
strOutData = BuildResponse(sItemData, CStr(Socket(Index).RemoteHostIP))
DoEvents
End Sub

Private Function BuildResponse(intStr As String, ip As String) As Variant

Set objStream = New ADODB.Stream
Set rsRequest = New ADODB.Recordset
Set cn = New ADODB.Connection
objStream.Open
objStream.WriteText intStr ' intStr is incoming XML from client
objStream.Position = 0
Set rsRequest = New ADODB.Recordset
rsRequest.Open objStream ' convert the incoming XML stream to a recordset
objStream.Close ' close the objStream and free up memory
' Write the recordset to the appropriate tables.
end sub

HTH

AMACycle