Passing a VB6 value to an Access Application

[Gnism]

Hello all,
Here it is…I have a VB6 application and in it, I would like to pass an ID from the VB6 form to a similar field in an access database. I currently “shell” out to open the database – but I want to open the database and populate the ID field on the database form with the ID from the VB6 app.

Any direction would be greatly appreciated...Thanks.

 

[macleod1021]

I'd use ADO for that. Make a reference in your app to Microsoft Active X Data Objects 2.7 then use the code below. I use DSN for the connection to make it easier.

Dim cn as adodb.connection
Dim rs as adodb.recordset
Dim str as string

Set cn as new adodb.connection
cn.connectionstring = "DSN=MyDSN"
cn.open

Set rs = new adodb.recordset
str = "Select * from tblMyAccessTable Where FieldID = " & txtFieldID
rs.Open str,cn,(there's 2 options here that I can't remember), adcmdtext
If rs.eof = false then
'Loop through what you want to do
End If
Set rs = nothing

cn.close

 

[Gnism]

Thanks for the quick response – unfortunately, I got called away for the afternoon and have yet to try your suggestion. I will post back on how things worked out...

 

[JerryKlmns]

If you just need to execute an update, the connection object is sufficient to accomplish this.

This function shall give you all the records updated.

Function UpdateMyRecord(strConn As String, strSQL As String) As Long
Dim Cnn As ADODB.connection
Dim lRecords As Long
Set Cnn = New ADODB.connection
With Cnn 
  .ConnectionString = strConn 
  .Open
  .Execute strSQL, lRecords, 129 'adCmdText +  adExecuteNoRecords
  .Close
End With
Set Cnn = Nothing
UpdateMyRecord = lRecords
End Function