ado dao recordsets hoe en wat

[manueld]

ik heb een sql database MSSQL 2008. ik gebruik access 2010 als frontend
tabellen zijn in simpele vorm hier weergegeven waarbij in de code de velden [ItemWarehouse],[ItemLocation] de criteria velden zijn die moeten matchen in beide tabellen en [ItemQtyReq] = gewenst aantal en [ItemQtyStock] = beschikbaarheid

tabel 1

[Partnumber],[criteriaveld],[gewenstaantal],[beschikbaaraantal]
1,a,2,0
1,b,2,0
2,a,1,0
3,a,1,0

tabel 2

[serienummer],[partnumber],[criteriaveld]
1,1,a
1,1,a
1,1,b
2,1,a

wat ik wil vanuit vba is dat alle records in de tabel (op de sql server) worden geevalueerd. er moet gekeken worden naar de totals voorraad welke voldoet aan de criteria dus in tabel1 de eerste moet gekeken worden hoeveel parts er in tabel 2 voldoen aan het partnumber en het criteriaveld en deze waarde moet geschreven worden in het beschikbareaantal veld van tabel1

dit is mij in sql niet gelukt maar probeer het nu in vba. de stappen die ik in gedachten had zijn

open tabel1
ga naar eerste record
lees partnumber en criteria
open tabel 2
doe een query met een count op partnumber where "criteria"
update record tabel1 waar bestchikbaarheid wordt ingevuld op basis van tabel2 count
close tabel2
ga naar volgende record en doe handelingen opnieuw totdat laatste record is afgehandeld.

ik kan op dit moment met een recordset de tabel 1 openen en doorlopen maar een update doet hij niet. wat doe ik verkeerd en kan dit ook makkelijker

verder heb ik het idee dat ik de diverse comando's voor ado en dao doorelkaar haal. is hier een duidelijk overzicht van. het liefst werk ik met ado omdat het nagenoeg allemaal op de SQL server gebeurt en ik geen linked tabels etc wil hebben ivm trage netwerk verbindingen (remote location)

Private Sub addstockqtytotblExRequestItems()
Dim cn As Object
'Dim rs As ADODB.Recordset
Dim sSQL As String
Dim PartNumber As String
Dim warehouse As String
Dim location As String
Dim requestID As Integer
Dim PK As Integer
Dim rs As Object
Dim strFile As String
Dim strSQL As String
    
    On Error Resume Next
        Call connection
(
        Set cn = CreateObject("ADODB.Connection")
        cn.Open strCon
        strSQL = "SELECT [PartNumber],[ItemWarehouse],[ItemLocation],[ItemQtyReq],[ItemQtyStock],[ItemReqId],[ItemPK] FROM [LMD_Process_Control].[dbo].[tblExRequestItems]"
       Set rs = CreateObject("ADODB.RECORDSET")
        rs.activeconnection = cn
        rs.Open strSQL
        
        rs.MoveFirst

Do Until rs.EOF

PartNumber = rs("PartNumber")
warehouse = rs("ItemWarehouse")
location = rs("ItemLocation")
requestID = rs("ItemReqId")
PK = rs("ItemPK")
MsgBox (PartNumber & warehouse & location & requestID & PK)'tijdelijke melding of hij er goed door loopt

'rs.Edit
rs![ItemQtyStock] = "10"     'moet een query resultaat zijn die de juiste aantallen geeft vanuit tabel 2
rs.Update
rs.MoveNext
Loop
DoCmd.SetWarnings True
rs.Close
        
        
        
        
        
        'qty = rs.Fields(1)
     On Error GoTo 0
MsgBox "Done"
Exit Sub
------------------------------

Sub connection()

'set connection. change here if connection changes

    strCon = "Provider=sqloledb;Server=ACH-SQL-V01;Database=LMD_Process_control; Uid=" & username & "; Trusted_Connection=yes; "

End Sub

 

[snb]

Sub M_snb()
    With CreateObject("ADODB.recordset")
        .Open "SELECT [PartNumber],[ItemWarehouse],[ItemLocation],[ItemQtyReq],[ItemQtyStock],[ItemReqId],[ItemPK] FROM [LMD_Process_Control].[dbo].[tblExRequestItems]", "Provider=sqloledb;Server=ACH-SQL-V01;Database=LMD_Process_control; Uid=" & UserName & "; Trusted_Connection=yes; "
        sn = .GetRows
    End With
    
    For jj = 0 To UBound(sn)
      If sn(4, jj) = 10 Then MsgBox sn(0, jj) & "_" & sn(1, jj) & "_" & sn(2, jj)
    Next
End Sub

 

[manueld]

geen idee wat de code allemaal doet die u opgeeft maar volgensmij doet hij niet wat ik vraag. ik wil geen msgbox ik wil dat een record wordt geupdate met waarden uit een andere tabel. dat zie ik niet terugkomen in uw code

 

[OctaFish]

Ik snap uit je voorbeeldje ook helemaal niet wat je nu wilt; maak eens bruikbare voorbeelden, het liefst natuurlijk met normale tabellen waar we niks aan hoeven te doen. Desnoods Excelletjes die ik in de db kan inlezen. We zijn geen tikgeiten .

 

[manueld]

oke even terug naar de basis dan vorm ik de rest wel zelf.

hoe kan ik vanuit access met vba zonder linked tables

een tabel op de server openen.
naar het eerste record gaan.
bepaald veld uitlezen
de tussenstap hieronder uitvoeren
een ander veld updaten
naar het volgende record gaan en alles herhalen tot laatste record is bereikt.

tussenstap
een 2e tabel openen en
een record zoeken waar de waarde van de eerste tabel het criteria is.
een veldwaarde uitlezen en gebruiken voor de te schrijven waarde in de eerste tabel

hoop dat het zo duidelijker is anders moet ik idd voorbeeld data gaan genereren

heeft er iemand een goed overzicht hoe je via vba data van een sql server kan lezen/schrijven/updaten etc. ik heb het voor elkaar gekregen in een eerder project maar heb het nog niet helemaal door wat nu precies wat is?

 

[manueld]

even de tabellen uit sql

[PartNumber],[ItemWarehouse] en [ItemLocation] zijn de velden die overeen moeten komen tussen bijde tabellen.

tabel 1 is de tabel die doorlopen moet worden. elk record moet in het veld [ItemQtyStock] aangevuld worden met de aantallen die gevonden worden in tabel 2

USE [LMD_Process_Control]
GO

/****** Object:  Table [dbo].[tblExRequestItems]    Script Date: 11/26/2015 13:59:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblExRequestItems](
	[PartNumber] [varchar](50) NOT NULL,
	[ItemWarehouse] [varchar](50) NOT NULL,
	[ItemLocation] [varchar](50) NOT NULL,
	[ItemQtyReq] [int] NOT NULL,
	[ItemQtyStock] [int] NOT NULL,
	[ItemReqId] [int] NULL,
	[ItemPK] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblExRequestItems] ADD  CONSTRAINT [DF_tblExRequestItems_ItemQtyStock]  DEFAULT ((0)) FOR [ItemQtyStock]
GO

tabel 2 is de tabel waar de voorraad in wordt bijgehouden. elk item heeft een eigen record. de tussenstap is bedoelt om in deze tabel te kijken hoeveel parts met hetzelfde nummer en location en warehouse er zijn. deze waarde moet geschreven worden in de andere tabel

USE [LMD_Process_Control]
GO

/****** Object:  Table [dbo].[tblExItem]    Script Date: 11/26/2015 14:00:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblExItem](
	[CSN] [varchar](50) NOT NULL,
	[PartNumber] [varchar](50) NOT NULL,
	[PalletNumber] [int] NOT NULL,
	[CreditNumber] [varchar](50) NULL,
	[ItemBlocked] [int] NOT NULL,
	[ItemblockedReason] [varchar](50) NULL,
	[ItemBlockedUser] [varchar](50) NULL,
	[ItemBlockedDate] [date] NULL,
	[ItemReqId] [int] NULL,
	[ItemTargetPallet] [int] NULL,
	[ItemWarehouse] [varchar](50) NULL,
	[ItemLocation] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblExItem] ADD  CONSTRAINT [DF_tblExItem_ItemBlocked]  DEFAULT ((0)) FOR [ItemBlocked]
GO

 

[OctaFish]

Dan heb ik nog steeds geen inhoud

 

[manueld]

informatie voor tabel 1. tabel 2 is nog leeg dus heb ik niets voor. kun je wel genereren met deze data


Part No. Warehouse Location QTY
90NUBW365E26527T138Y G10 CHOK00G100 1
90NB05M1-M00700 G10 CHOK00G100 5
90NB05W1-M00090 G10 CHOK00G100 181
90NB05W3-M00230 G10 CHOK00G100 18
90NB05W4-M00240 G10 CHOK00G100 25
90NB05W5-M00250 G10 CHOK00G100 3
90NB05W1-M00090 G10 CHOKB0G100 7
90NB05W3-M00230 G10 CHOKB0G100 5
90NB04J1-M00570 G10 CHOK00G100 6
90NB04M1-M00720 G10 CHOK00G100 1
90NB04M1-M01400 G10 CHOK00G100 1
90NB00M1-M00540 G10 CHOK00G100 1
90NB00M1-M01180 G10 CHOK00G100 2
90NB00M1-M01300 G10 CHOK00G100 1
90NB00N1-M01070 G10 CHOK00G100 3
90NB00N2-M02820 G10 CHOK00G100 1
90NB04K1-M01460 G10 CHOK00G100 1
90NB0892-M00030 G10 CHOK00G100 1
90NB06F1-M01750 G10 CHOK00G100 1
90NB06F1-M00650 G10 CHOKB0G100 1
90N2VL113E12I25D738Y G10 CHOK00G100 1
90NB04G1-M01120 G10 CHOK00G100 1
90NB04G2-M00220 G10 CHOK00G100 8
90NB04G2-M00290 G10 CHOK00G100 4
90NB04G2-M00480 G10 CHOK00G100 13
90NB04G2-M00620 G10 CHOK00G100 5

 

[OctaFish]

informatie voor tabel 1. tabel 2 is nog leeg dus heb ik niets voor. kun je wel genereren met deze data

Dat snap ik niet; in je vraag stel je duidelijk dat je wilt totaliseren op basis van de data in tabel 2. En die heb je dan nog niet?

 

[manueld]

Bekijk bijlage tabel 2.xlsx tabel 2

 

[manueld]

het is een nieuw systeem wat nog niet in gebruik is. er zal aan de hand van dit systeem geinventariseerd worden. in mijn vorige post heb ik zelf wat data gegenereerd welke bruikbaar moet zijn.