Can't load existing table from file?

[groleau]

I think I based this code on an MSDN article but I'm not sure.

SQL_Script(Program_Dir & "New_Cat_Records.sql")

Using Access_Conn As New System.Data.OleDb.OleDbConnection _
                         (Access_Conn_String)
   Access_Conn.Open()

   ' Due to internal semicolon, this can't be in above script
   Dim Access_Command As New System.Data.OleDb.OleDbCommand _
                         ("SELECT * INTO Categorized_Records " & _
                          "FROM [Text;DATABASE=C:\Temp].[For_270.txt]", _
                          Access_Conn)

   Access_Command.ExecuteNonQuery()

   Access_Conn.Close()

End Using

The SQL script deletes and re-creates the table.
(DELETE * would be just as good but this way I can change
the table def'n in the script.)
The problem is that the INSERT command fails with "Categorized_Records already exists"

If I allow Access to use the contents of the text file to guess at the table design, how is it going to know what the field names should be, the maximum length of the text fields, and which fields are currency?

Aside: I like the way we have to call them "currency" and "text" in the GUI but use "money" and "char" in SQL


--
Wes Groleau

http://www.Parkview.com/

 

[groleau]

Update: I've been searching the web all day, and I have
found at least eight different opinions on the correct SQL syntax. Interestingly, at least four of them
were in MSDN and Microsoft.com knowledge bases.

ALL of them failed, and no two got the same exception
message. The most interesting message was when the
SQL named all the fields in parens (standard INSERT format)
and the exception message complained that the INSERT
"contained the following unknown field name: 'F1'. "
(and of course, it did NOT contain an F1 even as a substring
of any field name!

I guess I'll have to call an Access macro to use an
import spec.

--
Wes Groleau

http://www.Parkview.com/

 

[groleau]

SOLVED:

After spending all day on this, losing count of the many different solutions, all of them complicated, and all of them failing (with half of the screwups coming FROM Microsft), I found a simple solution:

In Access, select File->Get External Data->Link Tables
Change the file type to text and select your input file [1]
Define the file format in the wizard.
Define your table name
Click Finish.

Now, you can change/rewrite the file anytime with VB or any other tool and Access instantly knows the current contents!

[1] If the filename is unpredictable, put a sample file
somewhere like C:\Temp\Temp.txt and copy into that as needed.

--
Wes Groleau

http://www.Parkview.com/

 

[JerryKlmns]

groleau

Allthough you seem to have solved this on your own, you might got an answer if you 've posted in the right forum of VB.NET forum796...

 

[groleau]

VB.Net was only calling Access and passing the SQL. Access and the Jet engine were refusing to run it.

And the solution is completely internal to Access.

--
Wes Groleau

http://www.Parkview.com/