Match fields with VBA

[Sean]

Hello,

Can VBA pull a variable from 1 table depending on a field from another. I
know an SQL query could do it, but I'm not sure if you can run an sql query
from within VBA. I'm controlling access from MS Project, so it all has to be
VBA driven. Here is what I think I need.

table 1 = MSP_TASKS (task information0
table 2 = MSP_LINKS (link information0

strname = Select [TASK_NAME] from [MSP_TASKS] where [MSP_TASKS]![TASK_ID] =
[MSP_LINKS]![LINK_PRED_ID]

Is something like this possible. Is there a better way? I know I could
loop through each table, but to do that for each record would be extremely
inefficient.

Thanks

 

[Tim Ferguson]

strname = Select TASK_NAME " & _
"from MSP_TASKS " & _
"where MSP_TASKS!TASK_ID = MSP_LINKS!LINK_PRED_ID"

A couple of problems:

a) SQL does not have a bang! operator. You access columns of a table
using a dot operator only: Msp_Tasks.TaskID

b) Msp_Links.Link_Pred_ID is not a single value -- it's a whole column of
values. Therefore this particular constraint is not meaningful even if it
were grammatic.

You can indeed create SQL commands in VBA -- in fact it's normal
procedure -- but there are two things to do first:

(a) make sure you know what you are trying to achieve ("pulling a value
from a table" does not make sense without _much_ more information), and

(b) get the SQL debugged first. The easiest way to do this is to use the
query designer in Access itself: then either switch to SQL view to copy
the code into your vba editor; or just save the thing as a querydef and
base your recordset on that.

Hope that helps



Tim F

 

[Sean]

I went a little different route, using a filter on recordset. However, I
can't seem to pass the variable into the mix.

This works.

Set rsttasks = db.OpenRecordset("SELECT * " & _
"FROM [MSP_Tasks] WHERE [TASK_UID] = 2")

But then I replace the 2 with a variable and it bombs

Dim var As Integer

var = 2

Set rsttasks = db.OpenRecordset("SELECT * " & _
"FROM [MSP_Tasks] WHERE [TASK_UID] = var")

I've also tried it with var defined as a string.

Thoughts? Thank you

 

[Sean]

I solved it.

I needed to put the sql statement into a string first, then call it. Here
is the code if anyone else searches for this issue.
Dim rstlinks As Recordset
Dim db As Database
Dim rsttasks As Recordset
Dim strsql As String
Dim idnum As Long
Dim result As String
Dim rsttemp2 As Recordset
Dim rsttemp As Recordset

Set db = CurrentDb()
Set rstlinks = db.OpenRecordset("MSP_LINKS")
Set rsttemp = db.OpenRecordset( _
"SELECT * FROM MSP_TASKS", dbOpenDynaset, dbReadOnly)

Do Until rstlinks.EOF

'OpenRecordsetOutput rsttemp
idnum = rstlinks.Fields("LINK_PRED_UID")

strsql = "TASK_UID = " & idnum & ""
rsttemp.Filter = strsql
Set rsttemp2 = rsttemp.OpenRecordset()
'OpenRecordsetOutput rsttemp2
result = rsttemp2.Fields("TASK_NAME")
rstlinks.MoveNext

Loop

I went a little different route, using a filter on recordset. However, I
can't seem to pass the variable into the mix.

This works.

Set rsttasks = db.OpenRecordset("SELECT * " & _
"FROM [MSP_Tasks] WHERE [TASK_UID] = 2")

But then I replace the 2 with a variable and it bombs

Dim var As Integer

var = 2

Set rsttasks = db.OpenRecordset("SELECT * " & _
"FROM [MSP_Tasks] WHERE [TASK_UID] = var")

I've also tried it with var defined as a string.

Thoughts? Thank you

A couple of problems:

a) SQL does not have a bang! operator. You access columns of a table
using a dot operator only: Msp_Tasks.TaskID

b) Msp_Links.Link_Pred_ID is not a single value -- it's a whole column of
values. Therefore this particular constraint is not meaningful even if it
were grammatic.

You can indeed create SQL commands in VBA -- in fact it's normal
procedure -- but there are two things to do first:

(a) make sure you know what you are trying to achieve ("pulling a value
from a table" does not make sense without _much_ more information), and

(b) get the SQL debugged first. The easiest way to do this is to use the
query designer in Access itself: then either switch to SQL view to copy
the code into your vba editor; or just save the thing as a querydef and
base your recordset on that.

Hope that helps



Tim F

 

[Tim Ferguson]

var = 2

Set rsttasks = db.OpenRecordset("SELECT * " & _
"FROM [MSP_Tasks] WHERE [TASK_UID] = var")

Set rsttasks = db.OpenRecordset( _
"SELECT * " & _
"FROM [MSP_Tasks] " & _
"WHERE [TASK_UID] = " & Format(var,"0"), _
dbOpenSnapshot, dbForwardOnly


The db engine knows nothing about vba local variables: it's the _value_
you have to pass, not the variable name.

The use of Format() is overkill for such a simple example, but is
certainly neccessary for dates, floating point values, etc.

You should always specify the type of recordset you want, if you love
your user and/ or your network manager.

Hope that helps


Tim F

 

[Larry Linson]

... but I'm not sure if you can run an sql query
from within VBA.

Of course, but programmatic access to the results would be a problem.

I'm controlling access from MS Project,
so it all has to be VBA driven. Here is
what I think I need. . . .

I obviously don't know _what_ you are doing, but you need not use COM
automation to "control Access" from Project unless you need more Access
features than just retrieving Task Names. You can use Data Access Objects
(DAO) code to access the Jet tables directly from Project.

Rather than proceed on a guess at what is best, if you would clarify in some
detail what you are trying to accomplish rather than just details of how you
want to do part, perhaps someone could be of even more assistance.

Larry Linson
Microsoft Access MVP