Importing from excel

[nerd19]

Im trying to update a table from three different excel tables, so what i have done is a union query to get the information i need from the three tables into one query table, Bulk Import. What I'm trying to do with the information in Bulk Import is to update/add to another table Tools v3. I want to add the record from Bulk Import to Tools v3 if it doesnt exist and update it if it does exist. So this is what i have so far:

Dim dbs As Database
Dim rst As DAO.Recordset
Dim Criteria As Form
Dim SQL As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Bulk Import")

'SQL = "SELECT [Bulk Import].[Tool Serial], [Bulk Import].[Size] FROM [Bulk Import]"

rst.MoveFirst
While Not rst.BOF
With rst
If DCount([Serial], [Tools v3], "[Tools v3].[Serial] = " & [Bulk Import].[Tool Serial]) = 0 Then
DoCmd.OpenQuery "add tool", acNormal, acAdd
Else
'DoCmd.OpenQuery "tool update", acNormal, acEdit
End If
.MoveNext
End With
Wend

I have done some research on what im doing and it seems that i almost have it but it just doesnt work still. I get an error that says "the field "|" doesnt exist." I'm somewhat new to vba coding so if someone can help me out that would be very much appreciated.

 

[PHV]

Have a look here:
thread701-1053292

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nerd19]

thanks that helps ... do you know why i would be getting a 'runtime error 3073 operation must use an updateable query'?

code for the update query:
UPDATE [Tools v3] RIGHT JOIN [Bulk Import] ON [Tools v3].Serial = [Bulk Import].[Tool Serial] SET [Tools v3].Serial = [Bulk Import].[Tool Serial], [Tools v3].[Size] = [Bulk Import].Size, [Tools v3].LastUpdate = Now(), [Tools v3].Status = "In", [Tools v3].Regrinder = Forms![Bulk Import]!Regrinder
WHERE ((([Tools v3].Serial) Is Not Null) AND (([Bulk Import].[Tool Serial]) Is Not Null));


then when i execute this i have an onClick event for a command button:
DoCmd.OpenQuery ("Bulk Update")

 

[nerd19]

Does it have to do with the type of join, because if i take out the join it will update the table but the user has to enter it, but it rids the error. Are there any other ways to do this?

 

[PHV]

What happens if you use a temporary table made from the union query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nerd19]

Is using the temporary table from the query just a matter of using a make table query and then deleting that table once you are done or is their a better way make one? Sry, might be basic but i have never done it before.

 

[nerd19]

This is what i have found to do but i keep getting a message saying that temporaryTbl is already created. Is this right?

Dim temp As TableDef

Set tempTbl = Db.CreateTableDef("temporaryTbl")
With temp
.Fields.Append .CreateField("Tool Serial", dbText)
.Fields.Append .CreateField("Size", dbDouble)

Db.TableDefs.Append temp
End With

 

[nerd19]

So here i what i have going for now:
I populate my temporary table with the values:
SQL = "INSERT INTO TempTbl ([Tool_Serial],Size) SELECT [Bulk Import update].[Tool Serial], [Bulk Import update].[Size] FROM [Bulk Import update]"
'DoCmd.RunSQL SQL1
Then i update the table with the new values from the temporary table, default values, and information from the form.
SQL1 = "UPDATE [Tools v3] SET [Tools v3].[Size] = TempTbl.[Size], [Tools v3].LastUpdate = Now(), [Tools v3].Status = 'In', [Tools v3].Regrinder = Forms![Bulk Import]!Regrinder WHERE ((([TempTbl].[Tool_Serial]) Is Not Null) AND ([Tools v3].[Serial] = [TempTbl].[Tool_Serial]));"

When i run my update code though, SQL1, i get a message saying that i am trying to update fields to null when i have then required. Any ideas why that might be?

 

[nerd19]

I got it .. thanks for the help