Cursors in Access?

[Adamus Turner]

Hi All,

My background is in SQL Server but I'm looking for a way to run a cursor in
MS Access 2003.

Something like:

DECLARE @BinLocation varchar(50)
DECLARE @PartNum varchar(50)

DECLARE c1 CURSOR FOR
SELECT BinLocation, PartNum
FROM ConsignmentBinLocation
Where PartNum = @PartNum

OPEN c1

FETCH NEXT FROM c1
INTO @BinLocation, @PartNum

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE TestTable
SET OEMBinLocation = @BinLocation
WHERE PartNum = @PartNum

FETCH NEXT FROM c1
INTO @BinLocation, @PartNum
END

CLOSE c1
DEALLOCATE c1

Is this possible?

Any help appreciated.

Adamus

 

[Douglas J. Steele]

You need to use VBA and open a recordset.

Having said that, though, I don't understand exactly what your sample code
is supposed to be doing, but I don't really see the need for the cursor.

 

[Adamus Turner via AccessMonster.com]

You need to use VBA and open a recordset.

Having said that, though, I don't understand exactly what your sample code
is supposed to be doing, but I don't really see the need for the cursor.

Hi All,

[quoted text clipped - 36 lines]

Adamus

I need a cursor for the update based on the primary key partnum.

Ok I put this vb code together and it works great when you execute it in the
module, but how do I call it from a macro?

Private conn As ADODB.Connection
Private strsql, strsql2 As String
Private rstFields As ADODB.Recordset
Private rstFields2 As ADODB.Recordset

Private Function GetBinLocation()
strsql = "Select * From TestTable"
strsql2 = "Select * From ConsignmentBinLocation"
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents And
Settings\Me\My Documents\db2.mdb;Persist Security Info=False;"

Set conn = New ADODB.Connection
Set rstFields = New ADODB.Recordset
conn.Open strg
Set rstFields.ActiveConnection = conn
rstFields.LockType = adLockOptimistic
rstFields.Open strsql, strg

Set conn2 = New ADODB.Connection
Set rstFields2 = New ADODB.Recordset
conn2.Open strg
rstFields2.LockType = adLockOptimistic

Set rstFields2.ActiveConnection = conn
rstFields2.CursorType = 3
rstFields2.Open strsql2, strg

Do While Not rstFields.EOF 'Test Table
Do While Not rstFields2.EOF 'ConsignmentBinLocation Table
If rstFields.Fields("PartNum").Value = rstFields2.Fields("PartNum").
Value Then
rstFields.Fields("OEMBinLocation").Value = rstFields2.Fields
("BinLocation").Value
rstFields2.MoveFirst
Exit Do
Else
rstFields2.MoveNext
End If
Loop
rstFields.MoveNext
Loop

rstFields.Close
rstFields2.Close
conn.Close
Set rstFields = Nothing
Set rstFields2 = Nothing
Set conn = Nothing
End Function

Sub GetBin()
Call GetBinLocation
End Sub

Thanks,

Adamus

 

[Douglas J. Steele]

I don't believe you can call it from a macro if it's defined as Private.

However, I still don't understand why you can't use a simple Update query.

UPDATE TestTable
INNER JOIN ConsignmentBinLocation
ON TestTable.PartNum = ConsignmentBinLocation.PartNum
SET OEMBinLocation = ConsignmentBinLocation.BinLocation

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You need to use VBA and open a recordset.

Having said that, though, I don't understand exactly what your sample code
is supposed to be doing, but I don't really see the need for the cursor.

Hi All,

[quoted text clipped - 36 lines]

Adamus

I need a cursor for the update based on the primary key partnum.

Ok I put this vb code together and it works great when you execute it in
the
module, but how do I call it from a macro?

Private conn As ADODB.Connection
Private strsql, strsql2 As String
Private rstFields As ADODB.Recordset
Private rstFields2 As ADODB.Recordset

Private Function GetBinLocation()
strsql = "Select * From TestTable"
strsql2 = "Select * From ConsignmentBinLocation"
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents And
Settings\Me\My Documents\db2.mdb;Persist Security Info=False;"

Set conn = New ADODB.Connection
Set rstFields = New ADODB.Recordset
conn.Open strg
Set rstFields.ActiveConnection = conn
rstFields.LockType = adLockOptimistic
rstFields.Open strsql, strg

Set conn2 = New ADODB.Connection
Set rstFields2 = New ADODB.Recordset
conn2.Open strg
rstFields2.LockType = adLockOptimistic

Set rstFields2.ActiveConnection = conn
rstFields2.CursorType = 3
rstFields2.Open strsql2, strg

Do While Not rstFields.EOF 'Test Table
Do While Not rstFields2.EOF 'ConsignmentBinLocation Table
If rstFields.Fields("PartNum").Value =
rstFields2.Fields("PartNum").
Value Then
rstFields.Fields("OEMBinLocation").Value = rstFields2.Fields
("BinLocation").Value
rstFields2.MoveFirst
Exit Do
Else
rstFields2.MoveNext
End If
Loop
rstFields.MoveNext
Loop

rstFields.Close
rstFields2.Close
conn.Close
Set rstFields = Nothing
Set rstFields2 = Nothing
Set conn = Nothing
End Function

Sub GetBin()
Call GetBinLocation
End Sub

Thanks,

Adamus

 

[Adamus Turner via AccessMonster.com]

I don't believe you can call it from a macro if it's defined as Private.

However, I still don't understand why you can't use a simple Update query.

UPDATE TestTable
INNER JOIN ConsignmentBinLocation
ON TestTable.PartNum = ConsignmentBinLocation.PartNum
SET OEMBinLocation = ConsignmentBinLocation.BinLocation
[quoted text clipped - 71 lines]

Adamus

Thanks for the reply Douglas,

I discovered that the function had to be public. It works fine now.

....and unless Access is differenct than SQL, you can't update a joined table.

Thanks,

Adamus

 

[Rick Brandt]

I don't believe you can call it from a macro if it's defined as
Private.

However, I still don't understand why you can't use a simple Update
query.

UPDATE TestTable
INNER JOIN ConsignmentBinLocation
ON TestTable.PartNum = ConsignmentBinLocation.PartNum
SET OEMBinLocation = ConsignmentBinLocation.BinLocation

You need to use VBA and open a recordset.

[quoted text clipped - 71 lines]

Adamus

Thanks for the reply Douglas,

I discovered that the function had to be public. It works fine now.

...and unless Access is differenct than SQL, you can't update a
joined table.

In many cases in fact you can. There is a help topic that discusses the
"rules".

 

[Adamus Turner via AccessMonster.com]

[quoted text clipped - 19 lines]

...and unless Access is differenct than SQL, you can't update a
joined table.

In many cases in fact you can. There is a help topic that discusses the
"rules".

Really? In Sql or Access? Can you provide a link so I can read these rules?

Thanks Douglas,

Adamus

 

[Rick Brandt]

I don't believe you can call it from a macro if it's defined as
Private.

[quoted text clipped - 19 lines]

...and unless Access is differenct than SQL, you can't update a
joined table.

In many cases in fact you can. There is a help topic that
discusses the "rules".

Really? In Sql or Access? Can you provide a link so I can read these
rules?

In Access 97 the help topic is named "When can I update data in a query?". I
assume a similar topic exists in the newer versions.

 

[Adamus Turner via AccessMonster.com]

[quoted text clipped - 7 lines]

Really? In Sql or Access? Can you provide a link so I can read these
rules?

In Access 97 the help topic is named "When can I update data in a query?". I
assume a similar topic exists in the newer versions.

Rick,

Of course you can update in a query...that's an update query...

I was looking for the thread and working code that allowed you update a
joined table...

Thanks,

Adamus