Help Executing Access query.

[Brett]

I'm trying to export an Access table to a CSV file. I'm using this code:

Function ExportAccessToText() As Boolean

Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT *
INTO [Text;DATABASE=e:\My
Documents\TextFiles].[Table3.csv] FROM Table_3", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

End Function

Which gives this error:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

On the line
AccessCommand.ExecuteNonQuery()

Any ideas what is wrong?

What is the file name produced by this syntax:
e:\My Documents\TextFiles].[Table3.csv]
Will it be named: e:\My Documents\TextFiles.Table3.csv

Thanks,
Brett

 

[Brett]

This code works fine but I'd like to know why the other code isn't working:

Dim fso = CreateObject("Scripting.FileSystemObject")
Dim txtfile As Object
txtfile = fso.OpenTextFile("C:\myFiles\Test\textfile.csv", 2, True)

Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myFiles\test\mydb.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT *
FROM [Table1]", AccessConn)

AccessCommand.ExecuteNonQuery()

Dim Reader As OleDbDataReader
Reader =
AccessCommand.ExecuteReader(CommandBehavior.CloseConnection)
If Reader.Read() Then
While Reader.Read
txtfile.WriteLine(Reader.Item("List Number") & "," &
Reader.Item("List Name"))
End While
End If

AccessConn.Close()

 

[Adamz5]

Hi Brett,

Basically copy and paste the code below and should work
'dont forget might need to import namespsaces can't be bothered typing
the whole thing

These are the things done see the 2 points

1) use a connection string

Dim accessconn As New OleDbConnection()

Dim constring As String
constring = ("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\trid32.mdb")

2) Try using a OleDbDataAdapter

like below:

Dim objadapter As New OleDbDataAdapter()
With objadapter
.SelectCommand = New OleDbCommand()
.SelectCommand.CommandText = ("SELECT * INTO
[Text;DATABASE=C:\aaa\].[Table3.csv] FROM TABLE_3")
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.Connection = New OleDbConnection(constring)
.SelectCommand.Connection.Open()
.SelectCommand.ExecuteNonQuery()
.SelectCommand.Connection.Close()
End With


Thanks
Adam

 

[Brett]

This seems to be quicker than my method. I still get

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

on the ExecuteNonQuery() line

I have to first make sure the file isn't there. That's the only time the
error is thrown.

Thanks,
Brett

 

[Brett]

There is only one table I can use from the Access database. All others
throw the error I posted earlier:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred

Any idea why that may be occurring or how I can troubleshoot it?

Thanks,
Brett

 

[Adamz5]

Hi Brett,

Use try catch as below, might also be worth checking you have the lates
data access cpmt installed MDAC 2.7

Use try

Catch exc As Exception
MsgBox(exc.Message)
End Try


Regards
Adam