insert problems

  • Thread starter Thread starter mi
  • Start date Start date
M

mi

Hi folks
I, as many others, have problems with insert ( at an existing table)
I’m not using objects.
I get no errors.
So, can anyone tell me what I’ve missed.

Thanks in advance
/Michelle

This is a small part of the code I am using in my module:
Option Compare Database

Function RunTheImport()

Dim rowid As Variant
Dim boxId As Variant
Dim magasineId As Variant
Dim magasineName As Variant
Dim pictureId As Variant
Dim bildNr As Variant
Dim descriptonId As Variant
Dim Datum As Variant

Dim SQLstr1 As String
Dim SQLstr As String
Dim ny As Boolean

ny = True
Set db = CurrentDb()
strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

intFile = FreeFile()

Open strLine For Input As #intFile

tblImportToSplit = strLine
'QUE, WHAT IS THIS
'strSQLImport = "SELECT tblImportToSplit.ImportData " _
& "FROM tblImportToSplit"

'--- Discard the first line in the text file (it contains only
headers)
Line Input #intFile, strLine

magasineNameExists = DLookup("magasineId", "Photos",
"[magasineName] ='" & magasineName & "' ")

Do While Not EOF(intFile)

Line Input #intFile, strLine
varArray = Split(strLine, ";")

magasineName = varArray(3)
varArray = Split(strLine, ";")
Datum = varArray(0)
bildNr = varArray(1)
description = varArray(2)
magasineId = "A"

If IsNull([magasineNameExists]) Then
If (ny = True) Then
boxId = DMax("boxId", "Photos", "")
'KOLLA UPP MAGASIN HÄR
boxId = boxId + 1
'--- pictureId
pictureId = 1
'----- rowId
rowid = DMax("rowId", "Photos", "")
rowid = rowid + 1
ny = False
magasineNameExists = 2
End If
Else
rowid = rowid + 1
magasineId = "A"
pictureId = pictureId + 1

End If
SQLstr2 = "INSERT INTO Photos (rowId, boxId, magasineId,
magasineName, description, pictureId, photoId) VALUES( " & _
rowid & "," & boxId & " , '" & magasineId & "', '" &
magasineName & "' , '" & description & "', " & pictureId & ", " &
bildNr & " ); "

CurrentDb.Execute SQLstr2
Loop
'Close #intFile

db.Close
'Set dbs = Nothing

Exit_RunTheImport:
Exit Function

Err_RunTheImport:
'Debug.Print Forms("Order Entry").OnInsert

'MsgBox (SQLstr)
MsgBox Err.description

End Function
 
Try adding the dbFailOnError option to generate a run-time error to see
what's going on:

CurrentDb.Execute SQLstr2, dbFailOnError

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi folks
I, as many others, have problems with insert ( at an existing table)
I’m not using objects.
I get no errors.
So, can anyone tell me what I’ve missed.

Thanks in advance
/Michelle

This is a small part of the code I am using in my module:
Option Compare Database

Function RunTheImport()

Dim rowid As Variant
Dim boxId As Variant
Dim magasineId As Variant
Dim magasineName As Variant
Dim pictureId As Variant
Dim bildNr As Variant
Dim descriptonId As Variant
Dim Datum As Variant

Dim SQLstr1 As String
Dim SQLstr As String
Dim ny As Boolean

ny = True
Set db = CurrentDb()
strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

intFile = FreeFile()

Open strLine For Input As #intFile

tblImportToSplit = strLine
'QUE, WHAT IS THIS
'strSQLImport = "SELECT tblImportToSplit.ImportData " _
& "FROM tblImportToSplit"

'--- Discard the first line in the text file (it contains only
headers)
Line Input #intFile, strLine

magasineNameExists = DLookup("magasineId", "Photos",
"[magasineName] ='" & magasineName & "' ")

Do While Not EOF(intFile)

Line Input #intFile, strLine
varArray = Split(strLine, ";")

magasineName = varArray(3)
varArray = Split(strLine, ";")
Datum = varArray(0)
bildNr = varArray(1)
description = varArray(2)
magasineId = "A"

If IsNull([magasineNameExists]) Then
If (ny = True) Then
boxId = DMax("boxId", "Photos", "")
'KOLLA UPP MAGASIN HÄR
boxId = boxId + 1
'--- pictureId
pictureId = 1
'----- rowId
rowid = DMax("rowId", "Photos", "")
rowid = rowid + 1
ny = False
magasineNameExists = 2
End If
Else
rowid = rowid + 1
magasineId = "A"
pictureId = pictureId + 1

End If
SQLstr2 = "INSERT INTO Photos (rowId, boxId, magasineId,
magasineName, description, pictureId, photoId) VALUES( " & _
rowid & "," & boxId & " , '" & magasineId & "', '" &
magasineName & "' , '" & description & "', " & pictureId & ", " &
bildNr & " ); "

CurrentDb.Execute SQLstr2
Loop
'Close #intFile

db.Close
'Set dbs = Nothing

Exit_RunTheImport:
Exit Function

Err_RunTheImport:
'Debug.Print Forms("Order Entry").OnInsert

'MsgBox (SQLstr)
MsgBox Err.description

End Function
 
Back
Top