update db, no errors , no changes

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

mi

Hi again
I have already asked this question earlier but I can’t find the
question. So I ask the same question once again.
What I’m trying to do is red data from a simple txt file and create a
new table with the data found in the text file

I can run the program with no errors. The string, SQLstr2, looks just
fine, neither does the execution with, CurrentDb.Execute SQLstr2,
shows any errors.

But the database looks as it did before, no changes.

Is there anyone that cat see what I’ve done wrong or missed to do.

Thanks in advance
/Michelle
-----
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 "New magasine saved"
Exit Sub

Err_cmdImport_Click:
MsgBox Err.description
End Sub
-----
Function RunTheImport()

On Error GoTo Err_RunTheImport

Dim intFile As String
Dim strFile As String
Dim varArray As Variant
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String
Dim strLine As String
Dim check As Boolean
Dim magasineId As Variant
Dim rowid As Variant
Dim magasineName As Variant
Dim boxId As Variant
Dim photoid As Variant
Dim pictureId As Variant
Dim SQLstr1 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
'--- Discard the first line in the text file (it contains just
headers)
Line Input #intFile, strLine

Do While Not EOF(intFile)
Line Input #intFile, strLine 'ska in någånstans varArray =
Split(strLine, ";")

varArray = Split(strLine, ";")
date = varArray(0)
photoid = varArray(1)
description = varArray(2)
magasineName = varArray(3)

If (ny = True) Then
boxId = DMax("boxId", "Photos", "")
magasineId = "A"
boxId = boxId + 1
pictureId = 1
rowid = DMax("rowId", "Photos", "")
rowid = rowid + 1
ny = False
Else
date = varArray(0)
photoid = varArray(1)
description = varArray(2)
magasineName = varArray(3)
rowid = rowid + 1
pictureId = pictureId + 1
End If

SQLstr2 = "INSERT INTO Photos ( rowId, boxId, magasineName,
description, photoId, pictureId, magasineId) VALUES(" & _
rowid & ", " & boxId & ", '" & magasineName & "',
'" & description & "', " & photoid & ", " & pictureId & ", '" &
magasineId & "'); "
CurrentDb.Execute SQLstr2, dbFailOnError
Loop
Close #intFile

Exit_RunTheImport:
Exit Function

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

End Function
 
m:
I have already asked this question earlier but I can't find the
question. So I ask the same question once again.
What I'm trying to do is red data from a simple txt file and
create a new table with the data found in the text file

I can run the program with no errors. The string, SQLstr2, looks
just fine, neither does the execution with, CurrentDb.Execute
SQLstr2, shows any errors.

But the database looks as it did before, no changes.

Is there anyone that cat see what I've done wrong or missed to do.
Click to expand...

What is the error?

And is there some reason why you can't just use DoCmd.TransferText
to import into a buffer table? It has an option that handles column
headers in the first row. The way you're doing it is incredibly
inefficient.
 
What is the error?

And is there some reason why you can't just use DoCmd.TransferText
to import into a buffer table? It has an option that handles column
headers in the first row. The way you're doing it is incredibly
inefficient.
Click to expand...

Hi David

I changed he line:
from CurrentDb.Execute SQLstr2
to
CurrentDb.Execute SQLstr2, dbFailOnError
Same result no changes what so ever.

The other question
What is the error?
Click to expand...
And is there some reason why you can't just use DoCmd.TransferText
to mport into a buffer table? It has an option that handles column
headers in the first row. The way you're doing it is incredibly
inefficient.
Click to expand...

The error is that i get no errors during the execution,
nor does the db change.

Thanks för replying.
/Michelle
 
m:
I changed he line:
from CurrentDb.Execute SQLstr2
to
CurrentDb.Execute SQLstr2, dbFailOnError
Same result no changes what so ever.
Click to expand...

What is CurrentDb.RecordsAffected immediately after
CurrentDB.Execute?
The other question



The error is that i get no errors during the execution,
nor does the db change.
Click to expand...

What about DoCmd.TransferText? Is there some reason you can't just
import the data directly and then process it once it's been
imported?
 
m:







What is CurrentDb.RecordsAffected immediately after
CurrentDB.Execute?



What about DoCmd.TransferText? Is there some reason you can't just
import the data directly and then process it once it's been
imported?

--
David W. Fenton                  http://www.dfenton.com/
contact via website only    http://www.dfenton.com/DFA/- Hide quoted text -

- Show quoted text -
Click to expand...

Hi David

I have to admit that you talking about things beyond my knowledge.
I haven't read any books about Access or Visual basic.
I've used the trail and error method.
To be more specific I don'y know what you talking about when you
talking about:
DoCmd.TransferText, CurrentDb.RecordsAffected, CurrentDb.Execute
SQLstr2, dbFailOnError. So I'mblittle bit confused.

You wonder what the error is after I run the CurrentDb.Execute
SQLstr2.
The only error I get is that the Db is unchanged.

Can you show me some pages to read and realize where to collect
accurent data.

Sorry and thanks
/Michelle
 
m:
I have to admit that you talking about things beyond my knowledge.
I haven't read any books about Access or Visual basic.
I've used the trail and error method.
To be more specific I don'y know what you talking about when you
talking about:
DoCmd.TransferText, CurrentDb.RecordsAffected, CurrentDb.Execute
SQLstr2, dbFailOnError. So I'mblittle bit confused.
Click to expand...

Maybe you should hire somebody who has the expertise you lack. It's
way too much to expect people in the newsgroup to walk you through
fixing the problems.
 
Maybe you should hire somebody who has the expertise you lack. It's
way too much to expect people in the newsgroup to walk you through
fixing the problems.
Click to expand...

I'm gonna try to do it in a sub instead of a function.
-mi
 
Back
Top