Executing and using a query in vb

[ulrik.stervbo]

Hi group,

I have a query which gives the IDs in TableA which are not already in
TableB. On each ID I need to do some string manipulation and update
TableB.

I am not sure how to work with the query which gives the IDs. Do I
have to save the query, or can I have the statement in the VB code?
How can I get to work on the result of the query?

I imagine that I will loop through a list with IDs, do what I need to
to with them, and update TableB using DoCmd.runSQL "statement".

Thanks for any help
Ulrik

 

[CompGeek78]

Hi group,

I have a query which gives the IDs in TableA which are not already in
TableB. On each ID I need to do some string manipulation and update
TableB.

I am not sure how to work with the query which gives the IDs. Do I
have to save the query, or can I have the statement in the VB code?
How can I get to work on the result of the query?

I imagine that I will loop through a list with IDs, do what I need to
to with them, and update TableB using DoCmd.runSQL "statement".

Thanks for any help
Ulrik

Depending on how complicated the manipulation you need to do to tableB
is, you may be able utilize an append query to a add data to tableB.
If you would give us more information about what it is you are trying
to accomplish (table structure, what manipulations you are doing, what
you want added to tableB, etc) we can be of more help.

Keven Denen

 

[ulrik.stervbo]

Depending on how complicated the manipulation you need to do to tableB
is, you may be able utilize an append query to a add data to tableB.
If you would give us more information about what it is you are trying
to accomplish (table structure, what manipulations you are doing, what
you want added to tableB, etc) we can be of more help.

Keven Denen

TableA:
seqID Lots of other info which is ignored in the query

TableB
seqID text NumberA NumberB source

I need the seqIDs which are not already in tableB. The seqIDs have for
form [text][numberA]-[numberB], this I need to split into the three
bits: [text], [number A], and [number B]. Depending on the [text] the
[source] col will be updated with a specific string.

Ulrik

 

[ulrik.stervbo]

Hi group,

I have a query which gives the IDs in TableA which are not already in
TableB. On each ID I need to do some string manipulation and update
TableB.

I am not sure how to work with the query which gives the IDs. Do I
have to save the query, or can I have the statement in the VB code?
How can I get to work on the result of the query?

I imagine that I will loop through a list with IDs, do what I need to
to with them, and update TableB using DoCmd.runSQL "statement".

Thanks for any help
Ulrik

I found a solution to extracting fields from a recordset from a query,
using DAO.Recordset:

Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

strSQL = "SELECT * " & _
"FROM table;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveLast
rst.MoveFirst
' The number of records
MsgBox rst.RecordCount

Do Until rst.EOF
' Get the fields out like this
f1 = rst.Fields(0)
f2 = rst.Fields(1)
' Do something

rst.MoveNext
Loop

rst.Close
dbs.Close

Set rst = Nothing
Set dbs = Nothing

 

[clarkcc]

what's going on?
"CompGeek78" <[email protected]> ????

Hi group,

I have a query which gives the IDs in TableA which are not already in
TableB. On each ID I need to do some string manipulation and update
TableB.

I am not sure how to work with the query which gives the IDs. Do I
have to save the query, or can I have the statement in the VB code?
How can I get to work on the result of the query?

I imagine that I will loop through a list with IDs, do what I need to
to with them, and update TableB using DoCmd.runSQL "statement".

Thanks for any help
Ulrik

Depending on how complicated the manipulation you need to do to tableB
is, you may be able utilize an append query to a add data to tableB.
If you would give us more information about what it is you are trying
to accomplish (table structure, what manipulations you are doing, what
you want added to tableB, etc) we can be of more help.

Keven Denen