INSERT results in nothing

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

mi

I’m using Access 2003. I’m reading data from a .txt file.
I’m building my SQL sentence:

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" & magasineName & "',"
& pictureId & "," & photoid & ");"

I execute my SQL sentence:

CurrentDb.Execute SQLstr1

The last thing I do in the code is:
Close #intFile
db.Close
Set db = Nothing

I get no errors or warnings, what so ever. But my db, Photos, looks
just as it did before.
What have I done wrong or missed to do?

Thanks in advance
/Michelle

(e-mail address removed)
 
Try this step to excecute:

CurrentDb.Execute SQLstr1, dbFailOnError

Then you'll get an error message/status back if the insert is failing. That
should help debug what is going on.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


I’m using Access 2003. I’m reading data from a .txt file.
I’m building my SQL sentence:

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" & magasineName & "',"
& pictureId & "," & photoid & ");"

I execute my SQL sentence:

CurrentDb.Execute SQLstr1

The last thing I do in the code is:
Close #intFile
db.Close
Set db = Nothing

I get no errors or warnings, what so ever. But my db, Photos, looks
just as it did before.
What have I done wrong or missed to do?

Thanks in advance
/Michelle

(e-mail address removed)
 
Hej Ken

Thanks for your answer.
I added dbFailOnError as you suggested once as an Integer, then as a
variant.
Sadly it resulted in nothing.

Any other ideas?

/Michelle
 
Then likely there is no error occurring for the insertion itself. Thus, I
suggest that your variables might not have any values? Put a breakpoint on
your code step that sets the SQLstr1 variable. When the code stops on that
step, hover over the variables that you're using for the VALUES data. What
values do you see for the variables?

--

Ken Snell
http://www.accessmvp.com/KDSnell/


Hej Ken

Thanks for your answer.
I added dbFailOnError as you suggested once as an Integer, then as a
variant.
Sadly it resulted in nothing.

Any other ideas?

/Michelle
 
I belive it's time to show the whole function.
Maby you'll find somethinh else that is completely wrong.

Thanks for helping me
/Michelle
-----------------------------------------------

Option Compare Database
IFunction RunTheImport()

On Error GoTo Err_RunTheImport

Dim intFile As String
Dim intFields As String
Dim strFile As String
Dim strLine As String
Dim strDir As String
Dim varArray As Variant
Dim MyArray(1 To 4) As String
Dim strPathFile As String
Dim strPath As String
Dim theStr As String
Dim file As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String
Dim rstImport As Recordset
Dim lngLoop As Long
Dim varFields As Variant
Dim varIN As Variant
Dim strBuffer As String
Dim pstrPathFile As String
Dim check As Variant
Dim magasineId As Variant
Dim rowid As Variant
Dim Datum As Variant
Dim boxId As Variant
Dim SQLstr1 As Variant
Dim SQLstr2 As Variant
Dim ny As Boolean
Dim dbFailOnError As Integer

ny = True
Set db = CurrentDb()

'--- Read data from form
strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

intFile = FreeFile()
Open strLine For Input As #intFile
tblImportToSplit = strLine

'--- Discard the first line in the text file, (it just contains
headers)
Line Input #intFile, strLine
varArray = Split(strLine, ";")
magasineName = varArray(3)
'--- Check if magasineName exists in db
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
strFile & "' ")

Do While EOF(intFile) = False
Line Input #intFile, strLine
varArray = Split(strLine, ";")

Datum = varArray(0)
photoid = varArray(1)
description = varArray(2)
magasineName = varArray(3)

'--- rowId
rowid = DMax("rowId", "Photos", "")
rowid = rowid + 1

'If IsNull([check]) Then
If (ny <> False) Then
ny = False

'--- magasineId
magasineId = "A"

'--- boxId
boxId = DMax("boxId", "Photos", "")
boxId = boxId + 1

'--- pictureId
pictureId = 1

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" &
magasineName & "'," & pictureId & "," & photoid & ");"
CurrentDb.Execute SQLstr1, dbFailOnError

SQLstr1 = "UPDATE [Photos] set [date] = '" & Datum & "'
where [boxId] = " & boxId & " AND [magasineName] = '" & newMagsineName
& "' AND [magasineId] = '" & magasineId & "' ;"
CurrentDb.Execute SQLstr1, dbFailOnError

ny = False
check = 2
Else
'--- pictureId
pictureId = pictureId + 1

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" &
magasineName & "'," & pictureId & "," & photoid & ");"
'CurrentDb.Execute SQLstr1
CurrentDb.Execute SQLstr1, dbFailOnError
SQLstr2 = "UPDATE [Photos] set [date] = '" & Datum & "'
where [boxId] = " & boxId & " AND [magasineName] = '" & newMagsineName
& "' AND [magasineId] = '" & magasineId & "' ;"
'CurrentDb.Execute SQLstr2
CurrentDb.Execute SQLstr1, dbFailOnError
End If
'End If
Loop
Close #intFile

db.Close
Set db = Nothing
'Set dbs = Nothing

Exit_RunTheImport:
Exit Function

Err_RunTheImport:
'MsgBox Err.description
Resume Exit_RunTheImport

End Function
 
Assuming you're running a Windows operating system, this step:
strDir = Forms![importWhat]![dir] & "/"

is using the wrong slash. Try this:
strDir = Forms![importWhat]![dir] & "\"


Also, I don't understand this step:
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
strFile & "' ")

Are you saying that you have the "magasineName" value in strFile? Your code
sets strFile to the name of the text file (without the ".txt" extension).
Shouldn't you be using magasineName instead:
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
magasineName & "' ")



--

Ken Snell
http://www.accessmvp.com/KDSnell/




mi said:
I belive it's time to show the whole function.
Maby you'll find somethinh else that is completely wrong.

Thanks for helping me
/Michelle
-----------------------------------------------

Option Compare Database
IFunction RunTheImport()

On Error GoTo Err_RunTheImport

Dim intFile As String
Dim intFields As String
Dim strFile As String
Dim strLine As String
Dim strDir As String
Dim varArray As Variant
Dim MyArray(1 To 4) As String
Dim strPathFile As String
Dim strPath As String
Dim theStr As String
Dim file As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String
Dim rstImport As Recordset
Dim lngLoop As Long
Dim varFields As Variant
Dim varIN As Variant
Dim strBuffer As String
Dim pstrPathFile As String
Dim check As Variant
Dim magasineId As Variant
Dim rowid As Variant
Dim Datum As Variant
Dim boxId As Variant
Dim SQLstr1 As Variant
Dim SQLstr2 As Variant
Dim ny As Boolean
Dim dbFailOnError As Integer

ny = True
Set db = CurrentDb()

'--- Read data from form
strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

intFile = FreeFile()
Open strLine For Input As #intFile
tblImportToSplit = strLine

'--- Discard the first line in the text file, (it just contains
headers)
Line Input #intFile, strLine
varArray = Split(strLine, ";")
magasineName = varArray(3)
'--- Check if magasineName exists in db
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
strFile & "' ")

Do While EOF(intFile) = False
Line Input #intFile, strLine
varArray = Split(strLine, ";")

Datum = varArray(0)
photoid = varArray(1)
description = varArray(2)
magasineName = varArray(3)

'--- rowId
rowid = DMax("rowId", "Photos", "")
rowid = rowid + 1

'If IsNull([check]) Then
If (ny <> False) Then
ny = False

'--- magasineId
magasineId = "A"

'--- boxId
boxId = DMax("boxId", "Photos", "")
boxId = boxId + 1

'--- pictureId
pictureId = 1

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" &
magasineName & "'," & pictureId & "," & photoid & ");"
CurrentDb.Execute SQLstr1, dbFailOnError

SQLstr1 = "UPDATE [Photos] set [date] = '" & Datum & "'
where [boxId] = " & boxId & " AND [magasineName] = '" & newMagsineName
& "' AND [magasineId] = '" & magasineId & "' ;"
CurrentDb.Execute SQLstr1, dbFailOnError

ny = False
check = 2
Else
'--- pictureId
pictureId = pictureId + 1

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" &
magasineName & "'," & pictureId & "," & photoid & ");"
'CurrentDb.Execute SQLstr1
CurrentDb.Execute SQLstr1, dbFailOnError
SQLstr2 = "UPDATE [Photos] set [date] = '" & Datum & "'
where [boxId] = " & boxId & " AND [magasineName] = '" & newMagsineName
& "' AND [magasineId] = '" & magasineId & "' ;"
'CurrentDb.Execute SQLstr2
CurrentDb.Execute SQLstr1, dbFailOnError
End If
'End If
Loop
Close #intFile

db.Close
Set db = Nothing
'Set dbs = Nothing

Exit_RunTheImport:
Exit Function

Err_RunTheImport:
'MsgBox Err.description
Resume Exit_RunTheImport

End Function
Click to expand...
 
Hi Ken

Really thanks a lot for your smart suggestions.
When I’m reading the file everything looks absolutely correct. I’ve
checking every variable,, just to see weather the data is correct or
not. My answer is YES the data is correct. .I has even checked that if
the variable is an integer it’s an integer that’s also checked in the
db.

Of course I’ve checked all the data I’m sending to the correct data to
me database.
So what’s more to check?

I do understand if you get tired of me. But frankly you ate the only
one that to say if I’ve got it right or not.

This line does matter:
Also, I don't understand this step:
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
strFile & "' ")
Silly as it is nor doe this:
is using the wrong slash. Try this:
strDir = Forms![importWhat]![dir] & "\"
Click to expand...

I’ve tried a lot of different things and nothings matter.
God night and sleep well

/Michelle
 
Hi Ken

Really thanks a lot for your smart suggestions.
When I’m reading the file everything looks absolutely correct. I’ve
checking every variable,, just to see weather the data is correct or
not. My answer is YES the data is correct. .I has even checked that if
the variable is an integer it’s an integer that’s also checked in the
db.

Of course I’ve checked all the data I’m sending to the correct data to
me database.
So what’s more to check?

I do understand if you get tired of me. But frankly you ate the only
one that to say if I’ve got it right or not.

This line does matter:
Also, I don't understand this step:
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
strFile & "' ")
Silly as it is nor doe this:
is using the wrong slash. Try this:
strDir = Forms![importWhat]![dir] & "\"
Click to expand...

I’ve tried a lot of different things and nothings matter.
God night and sleep well

/Michelle
 
Use Debug to pause your code after it sets the SQLstr1 variable.

In the Immediate Window, enter this expression and hit Enter key:
?SQLstr1

Let's see what the SQL string is that is being built. Does it look right for
the data that you expect?
--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hi Ken

Really thanks a lot for your smart suggestions.
When I’m reading the file everything looks absolutely correct. I’ve
checking every variable,, just to see weather the data is correct or
not. My answer is YES the data is correct. .I has even checked that if
the variable is an integer it’s an integer that’s also checked in the
db.

Of course I’ve checked all the data I’m sending to the correct data to
me database.
So what’s more to check?

I do understand if you get tired of me. But frankly you ate the only
one that to say if I’ve got it right or not.

This line does matter:
Also, I don't understand this step:
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
strFile & "' ")
Silly as it is nor doe this:
is using the wrong slash. Try this:
strDir = Forms![importWhat]![dir] & "\"
Click to expand...

I’ve tried a lot of different things and nothings matter.
God night and sleep well

/Michelle
 
Hi
Now I'm getting no errors.
till when I've run the code to it's end the database is unchanged.
pLEASE HELP ME.

Here's the code:

Option Compare Database

Function RunTheImport()

On Error GoTo Err_RunTheImport

Dim intFile As String
Dim intFields As String
Dim strFile As String
Dim strLine As String
Dim strDir As String
Dim varArray As Variant
Dim MyArray(1 To 4) As String
Dim strPathFile As String
Dim strPath As String
Dim theStr As String
Dim file As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String
Dim rstImport As Recordset
Dim lngLoop As Long
Dim varFields As Variant
Dim varIN As Variant
Dim strBuffer As String
Dim pstrPathFile As String
Dim check As Variant
Dim magasineId As Variant
Dim rowid As Variant
Dim Datum As Variant
Dim boxId As Variant
Dim SQLstr1 As Variant
Dim SQLstr2 As Variant
Dim ny As Boolean
Dim dbFailOnError As Integer
Dim X As Variant


'--- Read data from form
strDir = Forms![importWhat]![dir] & "\"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

ny = True

intFile = FreeFile()
Open strLine For Input As #intFile
tblImportToSplit = strLine

'--- Discard the first line in the text file, (it just contains
headers)
Line Input #intFile, strLine
varArray = Split(strLine, ";")
magasineName = varArray(3)
'--- Check if magasineName exists in db
check = DLookup("magasineId", "Photos", "[magasineName] = '" &
strFile & "' ")

Do While EOF(intFile) = False
Line Input #intFile, strLine
varArray = Split(strLine, ";")

Datum = varArray(0)
photoid = varArray(1)
description = varArray(2)
magasineName = varArray(3)

'--- rowId
rowid = DMax("rowId", "Photos", "")
rowid = rowid + 1

'If IsNull([check]) Then
If (ny <> False) Then
ny = False

'--- magasineId
magasineId = "A"

'--- boxId
boxId = DMax("boxId", "Photos", "")
boxId = boxId + 1

'--- pictureId
pictureId = 1

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" &
magasineName & "'," & pictureId & "," & photoid & ");"

CurrentDb.Execute SQLstr1

SQLstr1 = "UPDATE [Photos] set [date] = '" & Datum & "'
where [boxId] = " & boxId & " AND [magasineName] = '" & newMagsineName
& "' AND [magasineId] = '" & magasineId & "' ;"

CurrentDb.Execute SQLstr1

ny = False
check = 2

Else
'--- pictureId
pictureId = pictureId + 1

SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" &
magasineName & "'," & pictureId & "," & photoid & ");"

CurrentDb.Execute SQLstr1

SQLstr2 = "UPDATE [Photos] set [date] = '" & Datum & "'
where [boxId] = " & boxId & " AND [magasineName] = '" & newMagsineName
& "' AND [magasineId] = '" & magasineId & "' ;"

CurrentDb.Execute SQLstr2
End If
Loop
Close #intFile

'db.Close
Set db = Nothing
'Set dbs = Nothing

Exit_RunTheImport:
Exit Function

Err_RunTheImport:
MsgBox Err.description
MsgBox "Error " & Err.Number & vbCrLf & Err.sSQL, vbOKOnly

End Function
 
Oops some typos in previous message

Hi
Now I'm getting no errors.
till when I've run the code to it's end the database is unchanged.
pLEASE HELP ME.
Click to expand...

Should be:

No I'm getting no errors.
When I'm done running the code, the database is unchanged.
PLEASE HELP ME.


/Michelle
 
Hi Ken

I’m reading data from a .txt file. Which isI separated with ;
I’m building a SQL sentence: in SQLstr1 which is declared as
Dim SQLstr1 As Variant ,or
Dim SQLstr1 As String
same result

This is only two lines
SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" & magasineName & "',"
& pictureId & "," & photoid & ");"

I then execute my SQL sentence:

CurrentDb.Execute SQLstr1

I make a test what SQLstr1 contains (just for this case).
MsgBox CIAO "SQLstr1

It looks lide this:

CIAO INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES
(3800,102,'A','Berlin 06',1,2);

So, do you see any errors?

Thanks for all you help
Michelle
 
No, the SQL statement has proper syntax.

Are any of the fields in the Photos table insertion an autonumber data type?
--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hi Ken

I’m reading data from a .txt file. Which isI separated with ;
I’m building a SQL sentence: in SQLstr1 which is declared as
Dim SQLstr1 As Variant ,or
Dim SQLstr1 As String
same result

This is only two lines
SQLstr1 = "INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES (" & _
rowid & "," & boxId & ",'" & magasineId & "','" & magasineName & "',"
& pictureId & "," & photoid & ");"

I then execute my SQL sentence:

CurrentDb.Execute SQLstr1

I make a test what SQLstr1 contains (just for this case).
MsgBox CIAO "SQLstr1

It looks lide this:

CIAO INSERT INTO Photos
(rowId,boxId,magasineId,magasineName,pictureId,photoid) VALUES
(3800,102,'A','Berlin 06',1,2);

So, do you see any errors?

Thanks for all you help
Michelle
 
Back
Top