Import text file into access database

[kasteel]

Hi

I am using the following code to insert data from a text file into my access database. One of the columns in the database is called "e-mail".

I was getting syntax errors and was forced to rename the column name from e-mail to mail. Doing this seemed to have fixed my problem.

Is there another way of inserting the data into the database without having to rename the e-mail coumn to mail? I want to keep the column name as e-mail. Would appreciate any help / advise.

Dim cnn As New ADODB.Connection
        Dim Rec As New ADODB.Recordset
        Dim sqlString As String
        cnn.Open( _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\logs\import\Test.mdb;" & _
        "Jet OLEDB:Engine Type=4;")
        sqlString = "INSERT INTO [Sheet1] (FirstName, LastName, mail) SELECT FirstName, LastName, mail FROM [Text;DATABASE=C:\logs\import;].[outputfile1.txt]"
        cnn.Execute(sqlString)

 

[Tom]

The hyphen is a special character in Access, you would do better to rename the column email.

Error message when you use special characters in Access databases

Microsoft Access does not restrict the use of special characters such as a number sign (#), a period (.), or a quotation mark (") in the database object names or in the database field names. However, if you do use the special characters, you may experience unexpected errors. Therefore, Microsoft recommends that you do not use the special characters in the database object names in the Access database or in the database project. This article discusses the special characters that you must avoid because of known issues with these special characters.

When you work with Access or with some other application such as a Microsoft Visual Basic application or an Active Server Pages (ASP) application, you must avoid the following special characters:

Apostrophe '
Quotation mark "
Apostrophe '
At sign @
Grave accent `
Number sign #
Percent %
Greater than sign >
Less than sign <
Exclamation mark !
Period .
Brackets [ ]
Asterisk *
Dollar sign $
Semicolon ;
Colon :
Question mark ?
Caret ^
Braces { }
Plus sign +
Hyphen -
Equal sign =
Tilde ~
Backslash \

 

[kasteel]

Thanks

 

[kasteel]

Hi

Thanks.

I will avoid using special characters.

For now I managed to fix the code with the column name still staying e-mail:

  Private Sub SimpleButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SimpleButton1.Click
        Dim cnn As New ADODB.Connection
        Dim Rec As New ADODB.Recordset
        Dim sqlString As String
        cnn.Open( _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\logs\import\Test.mdb;" & _
        "Jet OLEDB:Engine Type=4;")
        sqlString = "INSERT INTO [Sheet1] (FirstName, LastName, [e-mail]) SELECT FirstName, LastName, [e-mail] FROM [Text;DATABASE=C:\logs\import;].[outputfile1.txt]"
        cnn.Execute(sqlString)

 

[jmcilhinney]

In future, please do us the courtesy of posting in the most appropriate forum for the topic and not just the first one you come to. Thread moved.

 

[kasteel]

Will do. Sorry