query db, delete based on results

S37N

Member
Joined
Jul 12, 2012
Messages
8
Programming Experience
1-3
ok, I have what I hope is an easy problem to fix. i'm try to query a db, store to a temp table, and then query the same db, inner joined with first query. please help. I'm currently getting "access cant find table/query" error. I've tried putting quotes around tmp, and that doesnt work either.

VB.NET:
[COLOR=#000088][LEFT]
[/LEFT]
[/COLOR][COLOR=#880000][FONT=inherit]'Set/Open Connection
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] con [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbConnection [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000088][FONT=inherit]New[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbConnection[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\youngje\Documents\SQL Server Management Studio\Projects\Nwind.accdb"[/FONT][/COLOR][COLOR=#666600][FONT=inherit])[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
con[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Open[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]
'Set TIMEID
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Yr[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Mnth[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] fRng [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As [/FONT][/COLOR][COLOR=#000088][FONT=inherit]String[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
Yr [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Year[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]Now[/FONT][/COLOR][COLOR=#666600][FONT=inherit])[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
 Mnth [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Format[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]Month[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]Now[/FONT][/COLOR][COLOR=#666600][FONT=inherit]),[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"00"[/FONT][/COLOR][COLOR=#666600][FONT=inherit])[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
fRng [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Yr [/FONT][/COLOR][COLOR=#666600][FONT=inherit]+[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Mnth [/FONT][/COLOR][COLOR=#666600][FONT=inherit]+[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"00"
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]
'Query 1/Fill Temp Table (tmp)
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] cmdA [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbCommand [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000088][FONT=inherit]New[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbCommand[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"SELECT PRODUCT, SHIPTO, TIMEID "[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] _
[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"FROM tblFactSales "[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] _
[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"WHERE (BILLTO = 'INPUT_BILLTO') AND (BRANCHPLANT = 'INPUT_BRANCHPLANT') AND (FRTHANDLE = 'INPUT_FRTHANDLE') AND (DATATYPE = 'FORECAST') AND (TIMEID > '"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] fRng [/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"' )"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] _
[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"AND (SIGNEDDATA >= - .01) AND (SIGNEDDATA <= .01) AND (SALESDATA = 'short_tons')"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] con[/FONT][/COLOR][COLOR=#666600][FONT=inherit])
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]
Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] sda [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbDataAdapter [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000088][FONT=inherit]New[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbDataAdapter[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]cmdA[/FONT][/COLOR][COLOR=#666600][FONT=inherit])
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] ds [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] DataSet [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000088][FONT=inherit]New[/FONT][/COLOR][COLOR=#000000][FONT=inherit] DataSet[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
sda[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Fill[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]ds[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"tmp"[/FONT][/COLOR][COLOR=#666600][FONT=inherit])
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]
'Query 2, joined with Query 1
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] cmdB [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbCommand [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000088][FONT=inherit]New[/FONT][/COLOR][COLOR=#000000][FONT=inherit] OleDbCommand[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"DELETE tblFactSales "[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] _
[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"FROM tmp INNER JOIN tblFactSales "[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] _
[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"ON tmp.PRODUCT = tblFactSales.PRODUCT AND tmp.SHIPTO = tblFactSales.SHIPTO AND tmp.TIMEID = tblFactSales.TIMEID "[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] _
[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"WHERE (DATATYPE = 'FORECAST') AND (TIMEID > '"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#000000][FONT=inherit] fRng [/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"' )"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] con[/FONT][/COLOR][COLOR=#666600][FONT=inherit])
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]
'Execute Queries[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
cmdA[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]ExecuteNonQuery[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
cmdB[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]ExecuteNonQuery[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]
'Clean Up[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
cmdA[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Dispose[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
cmdB[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Dispose[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
con[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Close[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
GC[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Collect[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()
[/FONT][/COLOR][COLOR=#880000][FONT=inherit]
'Confirmation[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
MessageBox[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Show[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Records Removed Successfully."[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Clear Complete"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] _
MessageBoxButtons[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]OK[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] MessageBoxIcon[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Information[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] MessageBoxDefaultButton[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Button1[/FONT][/COLOR][COLOR=#666600][FONT=inherit]) [/FONT][/COLOR][LEFT]
[/LEFT]

 
Last edited:

Dunfiddlin

Well-known member
Joined
Jun 15, 2012
Messages
253
Programming Experience
5-10
Can't find it cos it doesn't exist. You're using it as a source table but you've never actually created it or filled it. You execute cmdA after you've attempted to use its results in any case.
 

S37N

Member
Joined
Jul 12, 2012
Messages
8
Programming Experience
1-3
havent totally fixed it, but I think I'm getting closer. I'm getting a "Record is deleted" error now on cmdB.ExecuteNonQuery. It creates & populates the tmp table, but nothing gets deleted from the main table.


Private Sub cmdDelete_Click(sender As System.Object, e As System.EventArgs) Handles cmdDelete.Click

'Set/Open Connection
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\youngje\Documents\SQL Server Management Studio\Projects\Nwind.accdb")
con.Open()

'Set TIMEID
Dim Yr, Mnth, fRng As String
Yr = Year(Now)
Mnth = Format(Month(Now), "00")
fRng = Yr + Mnth + "00"

'SELECT, to find unique IDs (PRODUCT, SHIPTO, TIMEID, DATATYPE)
Dim cmdA As OleDbCommand = New OleDbCommand("SELECT PRODUCT, SHIPTO, TIMEID, DATATYPE INTO tmp IN 'C:\Users\youngje\Documents\SQL Server Management Studio\Projects\Nwind.accdb'" & _
"FROM tblFactSales " & _
"WHERE (BILLTO = 'INPUT_BILLTO') AND (BRANCHPLANT = 'INPUT_BRANCHPLANT') AND (FRTHANDLE = 'INPUT_FRTHANDLE') AND (DATATYPE = 'FORECAST') AND (TIMEID >= '" & fRng & "' )" & _
"AND (SIGNEDDATA >= - .01) AND (SIGNEDDATA <= .01) AND (SALESDATA = 'short_tons')", con)

'DELETE, joined with cmdA results
Dim cmdB As OleDbCommand = New OleDbCommand("DELETE tblFactSales.* " & _
"FROM tblFactSales INNER JOIN tmp T " & _
"ON tblFactSales.PRODUCT=T.PRODUCT AND tblFactSales.SHIPTO=T.SHIPTO AND tblFactSales.TIMEID=T.TIMEID", con)

'Execute Queries
cmdA.ExecuteNonQuery()
cmdB.ExecuteNonQuery()

'Clean Up
cmdA.Dispose()
cmdB.Dispose()
con.Close()
GC.Collect()

'Confirmation
MessageBox.Show("Records Removed Successfully.", "Clear Complete", _
MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
End Sub
 
Top Bottom