query db, delete based on results

S

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:
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote

Similar threads

D
  • Question
Question What does no value given for one or more required parameters mean? How to fix?
Replies
5
Views
6K
jmcilhinney
jmcilhinney
S
  • Question
Strange Timeout Error
Replies
5
Views
3K
S37N
S
J
  • Question
Question Unable to save survey answers into database
Replies
2
Views
4K
Jasmine Koh
J
S
  • Question
Question SQL EXPRESS Connection Problem
Replies
2
Views
3K
S37N
S
M
  • Question
Question Deleting Record based on name from access database.
Replies
0
Views
1K
mashalyy
M

Share this page

Latest posts

Back
Top