PLEASE HELP, insert

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

mi

Please help me anyone.!!

Im trying toread a txt-file and do insert in my database, Access.
I get no errors. I run thrue the whole code just fine but when i've
finished it all I've have nothing new in my database.

i start with this a sub.

Private Sub cmdImport_Click()
On Error GoTo Err_cmdImport_Click

Dim bUserSaved As Boolean

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.GoToRecord , , acNewRec

RunTheImport

Exit_cmdImport_Click:
MsgBox "Nytt magasin sparat"
Exit Sub

Err_cmdImport_Click:
MsgBox Err.description

End Sub

In which call the module' RunTheImport'

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

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)

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 (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

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
Resume Exit_RunTheImport

End Function

I get no errors but still the db s unchange.

Please omone help mr

Reagars
/micjhelle
 
Code:
		CurrentDb.Execute SQLstr1, dbFailOnError
Hello! Always use the dbFailOnError when using the Execute method.
 
Back
Top