problems with jet-sql and insert statement
i am in the process of migrating a non-relational database to a relational database (hence my plethora of sql questions :o ). i was on the verge of being able to actually accomplish a lot of work instead of just figuring things out, but then "insert" came along and ruined my day :mad: . after a lot of playing, i found that a vb app connecting to a access database did not support a full outer join. to get around this, i did both a left outer join and a right outer join and inserted them into the same table. this table is one of three copies of the same table. they all have the same structure and allow for the same data. my plan is from this point merely insert the data using the distinct keyword into another copy of the table (again same structure and all). but, alas :eek: ! i get an error telling me that one of my fields (could be more than one actually) can't squeeze all of the necessary data into it. my questions :confused: are, does anyone know of any such problem with access/jet and a fix for it AND/OR does anyone know of another way of accomplishing a full join connecting to an access/jet database? i included my code below. (for anyone keeping track, i just figured out the smilies the other day after 40+ posts and am therefore, required to use them at every turn).
'create connection strings and then establish connections
strEquip = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\my documents\migrate\equipment.mdb"
conEquip.Open strEquip
'update txtStatus.Text
strStatus = strStatus & _
"Connection to Equipment.mdb has been established" & vbCrLf
UpdateStatus strStatus
'open recordsets
sqlEquip = "INSERT INTO tblEquipTrans1 " & _
"SELECT * FROM tblContract A " & _
"LEFT JOIN tblFeb7 B " & _
"ON A.Contract_ControlNo = B.Feb7_ControlNo"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblContract has been Left Joined to tblFeb7" & vbCrLf
UpdateStatus strStatus
sqlEquip = "INSERT INTO tblEquipTrans1 " & _
"SELECT * FROM tblContract A " & _
"RIGHT JOIN tblFeb7 B " & _
"ON A.Contract_ControlNo = B.Feb7_ControlNo"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblContract has been Right Joined to tblFeb7" & vbCrLf
UpdateStatus strStatus
sqlEquip = "INSERT INTO tblEquipTrans2 " & _
"SELECT DISTINCT * FROM tblEquipTrans1"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblContract and tblFeb7 are fully joined in tblEquipTrans2" & vbCrLf
UpdateStatus strStatus
sqlEquip = "DELETE FROM tblEquipTrans1"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblEquipTrans1 has been reset" & vbCrLf
UpdateStatus strStatus
sqlEquip = "INSERT INTO tblEquipTrans1 " & _
"SELECT * FROM tblEquipTrans2 A " & _
"LEFT JOIN tblFF B " & _
"ON A.Contract_ControlNo = B.FF_ControlNo"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblEquipTrans2 has been Left Joined with tblFF" & vbCrLf
UpdateStatus strStatus
sqlEquip = "INSERT INTO tblEquipTrans1 " & _
"SELECT * FROM tblEquipTrans2 A " & _
"RIGHT JOIN tblFF B " & _
"ON A.Contract_ControlNo = B.FF_ControlNo"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblEquipTrans2 has been Right Joined with tblFF" & vbCrLf
UpdateStatus strStatus
sqlEquip = "INSERT INTO tblEquipment " & _
"SELECT DISTINCT * FROM tblEquipTrans1"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblEquipTrans2 and tblFF are fully joined in tblEquipment" & vbCrLf
UpdateStatus strStatus
sqlEquip = "DELETE FROM tblEquipTrans1"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblEquipTrans1 has been reset" & vbCrLf
UpdateStatus strStatus
sqlEquip = "DELETE FROM tblEquipTrans2"
rsEquip.Open sqlEquip, conEquip, adOpenStatic, adLockOptimistic
'update txtStatus.Text
strStatus = strStatus & _
"tblEquipTrans2 has been reset" & vbCrLf
UpdateStatus strStatus
Set conEquip = Nothing
Set rsEquip = Nothing
strStatus = ""
