Writing DataTable (100+ Columns) Back To MS Access

[jsurpless]

Hi

I've been using the following code with my .NET application which reads information from Access

Dim MSAccess_Connection As OleDb.OleDbConnection
Dim strMSAccess_Connection_SQL As String = ""
Dim strDatabaseTable_SQL As String = ""
Dim MSAccess_DatabaseTable As New DataTable

strMSAccess_Connection_SQL = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDatabaseFilePath

MSAccess_Connection = New OleDbConnection(strMSAccess_Connection_SQL)

strDatabaseTable_SQL = "SELECT * FROM [" & strDatabaseTable & "]"

MSAccess_Database_Adapter = New OleDbDataAdapter(strDatabaseTable_SQL, MSAccess_Connection)

MSAccess_Database_Adapter.Fill(MSAccess_DatabaseTable)

I then manipulate MSAccess_DatabaseTable in a variety of ways (adding, deleting and modifying entries) and then I want to write it back to Access

I've been doing it this way

Dim MSAccess_DatabaseTable_Changes As DataTable

 MSAccess_DatabaseTable_Changes = MSAccess_DatabaseTable.GetChanges

If Not IsNothing(MSAccess_DatabaseTable_Changes) Then

Dim MSAccess_Connection As OleDb.OleDbConnection
Dim strMSAccess_Connection_SQL As String = ""
Dim strDatabaseTable_SQL As String = ""
Dim MSAccess_DatabaseTable As New DataTable

strMSAccess_Connection_SQL = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDatabaseFilePath

MSAccess_Connection = New OleDbConnection(strMSAccess_Connection_SQL)

strDatabaseTable_SQL = "SELECT * FROM [" & strDatabaseTable & "]"

MSAccess_Database_Adapter = New OleDbDataAdapter(strDatabaseTable_SQL, MSAccess_Connection)

MSAccess_Database_Builder = New OleDbCommandBuilder(MSAccess_Database_Adapter)

MSAccess_Database_Builder.QuotePrefix = "["
MSAccess_Database_Builder.QuoteSuffix = "]"

MSAccess_Database_Adapter.UpdateCommand = MSAccess_Database_Builder.GetUpdateCommand

MSAccess_Database_Adapter.Update(MSAccess_DatabaseTable_Changes)

End If

This worked for years and then my user surprised me with an MS Access table that has more than 100 columns. My understanding is that the method I am using above is limited to 100 columns (I verified that removing sufficient columns remedies the issue).

Is there a way around this? Is there a better way to read all of the input data in one swoop, manipulate the resulting datatable as needed and then write it back in one swoop?

Many thanks in advance!

 

[jmcilhinney]

I'm assuming that that 100-column limitation is on automatic SQL generation by a command builder, although I wasn't able to confirm that with a quick search. Do you know whether that is the case? If so then you would simply not be able to use a command builder if you want to be able to support tables with greater than 100 columns. You'd have to write code to generate the action commands yourself. Effectively, you'd be writing code to do what the command builder does, but without the limitation of 100 columns. To be able to do that, you might start by reading the source code for the relevant command builder class(es). If you find that the limitation originates at a lower level then you may need to come up with a different way to do it, e.g. by examining the schema of the DataTable. That may or may not be as reliable though.