SteveS
Posts: 41
|
| Posted: 03/29/2004, 5:00 AM |
|
Hi,
I'm using the following code to import a CSV file to a table. If the table doesn't exisit it creates and imports the data, however, if I want the code to append the CSV data to the table I get the following error:
Microsoft JET Database Engine error '80040e14'
Table 'Pupil_Names' already exists.
/barit/csv/csvtoaccess.asp, line 21
How do I get the code to append? Thanks!
_________________________________________
Original code from Planet
_________________________________________
<%
'Making a connection with the database
set objConn= Server.CreateObject("ADODB.Connection")
strDBPath = Server.MapPath("./mdb_test.mdb")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
' Path of the CSV file, curently i am accepting the root file itself.
Dim csv_path
csv_path = Server.MapPath("./") & "\"
' SQL String for inserting CSV data into the Database
SQL = "SELECT * INTO Pupil_Names FROM [test_csv.csv] IN """ & csv_path & """ ""TEXT;"""
' SQL String for inserting CSV data into the Database
'SQL = "INSERT INTO Pupil_Names SELECT * FROM [test_csv.csv] IN """ & csv_path & """ ""TEXT;"""
'Executing the SQL Statement
objConn.Execute SQL
'Closing down the connection
objConn.Close
Set objConn = Nothing
response.write("Complete")
%>
|
 |
 |
DonB
|
| Posted: 03/29/2004, 5:53 AM |
|
I think Access is being clever behinds the scenes and invoking its "import
to a table" mechanism, not a conventional SQL INSERT (because it recogizes
the source data is from a file). It's possible that if the table exists,
you could use UPDATE instead of INSERT, but that's only a guess.
Using this method for importing is probably best done by specifying an
intermediate table (like perhaps "t_Pupil_Names") and following that with
another SQL INSERT to Pupil_Names from t_Pupil_Names. You might find this
preferable because the imported data could then be reviewed for accuracy
before getting loaded into the "real" table.
You can then execute a DROP TABLE t_Pupil_Names before each csv file gets
loaded, thus avoiding the error.
--
DonB
http://www.gotodon.com/ccbth
"SteveS" <SteveS@forum.codecharge> wrote in message
news:640681ddebb5d4@news.codecharge.com...
> Hi,
>
> I'm using the following code to import a CSV file to a table. If the
table doesn't exisit it creates and imports the data, however, if I want the
code to append the CSV data to the table I get the following error:
>
> Microsoft JET Database Engine error '80040e14'
>
> Table 'Pupil_Names' already exists.
>
> /barit/csv/csvtoaccess.asp, line 21
>
> How do I get the code to append? Thanks!
>
> _________________________________________
> Original code from Planet
> _________________________________________
>
> <%
> 'Making a connection with the database
>
> set objConn= Server.CreateObject("ADODB.Connection")
>
> strDBPath = Server.MapPath("./mdb_test.mdb")
> objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath &
";"
>
>
> ' Path of the CSV file, curently i am accepting the root file itself.
> Dim csv_path
> csv_path = Server.MapPath("./") & "\"
>
> ' SQL String for inserting CSV data into the Database
> SQL = "SELECT * INTO Pupil_Names FROM [test_csv.csv] IN """ & csv_path
& """ ""TEXT;"""
>
> ' SQL String for inserting CSV data into the Database
> 'SQL = "INSERT INTO Pupil_Names SELECT * FROM [test_csv.csv] IN """ &
csv_path & """ ""TEXT;"""
>
> 'Executing the SQL Statement
> objConn.Execute SQL
>
> 'Closing down the connection
> objConn.Close
> Set objConn = Nothing
> response.write("Complete")
> %>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
