Importing string with data to accaess

[michellesihdu]

Hi all

My name is Michelle.
I'm having some problems importing data to my Access DB. I¡¦ve imported
it first from an excelfile, which I made a txt file of, which is just
fine, but when I want to take that data and put it in my DB all I get
is syntax error. I have to do some extra stuff with the data from the
excelfile since it doesn¡¦t contain all columns.

My code looks like this:
strSQLImport = "SELECT tblImportToSplit.ImportData " _
& "FROM tblImportToSplit"

Line Input #intFile, strFile
Do While EOF(intFile) = False

varArray = Split(strFile, ";")
date = varArray(0)
bildNr = varArray(1)
description = varArray(2)
magasineName = varArray(3)

-----„³ this works just fine, I get all data that¡¦s in the excelfile.

'--- Check if magasineName exists
check = DLookup("magasineId", "Photos", "[magasineName] ='" &
magasineName & "' ")
If IsNull([check]) Then
'MsgBox "New magasine"
'----- boxId
boxId = DMax("boxId", "Photos", "")

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

'--- MagasineId
magasineId = "A"

'--- pictureId
pictureId = 1
boxId = boxId
magasineName = magasineName
magasineId = magasineId
x = Null

-----„³ I¡¦ve tried all of these INSERT INTO, but I just get ¡§SYNTAX
ERROR insert into

'SQLstr1 = "INSERT INTO Photos [(boxId[, magasineName
[,magasineId]])] VALUES (boxId[, magasineName[,magasineId]])
'SQLstr1 = "INSERT INTO Photos [(magasineName])] values '" &
boxId & "', '" & magasineName & "', '" & magasineId & "' ;"
'SQLstr1 = "INSERT INTO tblExcelData SELECT * FROM " &
tdf.Name
'SQLstr1 = "INSERT INTO Photos [(boxId[, magasineName[,
magasineId]]])] values " & boxId & "', '" & magsineName & "', '" &
magsineId & "' ;"
SQLstr1 = "SELECT * INTO [Photos] FROM " & tblImportToSplit &
";"
'SQLstr1 = "INSERT INTO [Photos] (x,
bildNr,date,description,magsineName,x, x, x,
thisRowId,,magsineId,boxId,pictureId); "
''SQLstr1 = "INSERT INTO [Photos] select " & (varArray(9));

CurrentDb.Execute SQLstr1

Any help would make me happy.

Thanks in advance
/Michelle

 

[pietlinden]

a couple of ideas:
1. skip the export to text option completely and use
TransferSpreadsheet and an import specification. OR
2. link to the Excel file and import directly.

what's with the building of the SQL Insert statements without a
corresponding
DbEngine(0)(0).Execute strSQL

?

I mean, if you're going to go through all the trouble of building the
SQL statement, why not USE it?

 

[michellesihdu]

a couple of ideas:
1. skip the export to text option completely and use
TransferSpreadsheet and an import specification. OR
2. link to the Excel file and import directly.

what's with the building of the SQL Insert statements without a
corresponding
DbEngine(0)(0).Execute strSQL

?

I mean, if you're going to go through all the trouble of building the
SQL statement, why not USE it?

Thanks for anwering.

The thing is just that the Excelfile contains only 4 columns and the
table contains 10 colums which one is the key. So I have to some
calculation before it's ready for saving in the table.

Rewgards
/Michelle

 

[pietlinden]

Thanks for anwering.

The thing is just that the Excelfile contains only 4 columns and the
table contains 10 colums which one is the key. So I have to some
calculation before it's ready for saving in the table.

Rewgards
/Michelle

So you create an import specification and map the 4 columns you want
to save in the table. What's with the other 6 columns? If they're
calculated values, why store them at all?