• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

MS Access Select statement

I need to move data from an MS Access database into a SQL database. The following code does work.
Dim AccessConn As New System.Data.Odbc.OdbcConnection
Dim AccessCmd As New System.Data.Odbc.OdbcCommand
Dim AccessReader As System.Data.Odbc.OdbcDataReader
Dim ParmString As String = String.Empty
Dim FieldNameString As String = String.Empty
Dim i As Integer = 0
Dim InsertString As String = String.Empty
Dim FirstRecordBoolean As Boolean = True

ParmString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & FileNameString & ";Exclusive=1;Uid=admin;Pwd=;"
AccessConn.ConnectionString = ParmString
AccessCmd.Connection = AccessConn
ParmString = "Select * " & _
        "from TaxRates "

        AccessCmd.CommandText = ParmString
        AccessReader = AccessCmd.ExecuteReader
        InsertString = "("
        Do While AccessReader.Read
            For i = 0 To AccessReader.FieldCount - 1
                FieldNameString = AccessReader.GetName(i)
                If FirstRecordBoolean Then
                    FirstRecordBoolean = False
                    InsertString = InsertString & FieldNameString
                    InsertString = InsertString & ", " & FieldNameString
                End If

            InsertString = Mid(InsertString, 1, InsertString.Length - 0) & ")"
            Exit Do

Open in new window

However, when I try to enumerate the acutal field names. I receive an error.
E.G. Select [Signature Code] From TaxRates

This Select statement generates the following error:
Error [07002][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The Access table is full of field names that contain spaces, parenthesis, forward slashes, etc. In SQL server I deal with this by enclosing the field names in square brackets but that does not seem to work with Access.

How do I select field names that contain, spaces, reserved words, etc?

1 Solution
Rey Obrero (Capricorn1)Commented:
this format  
 Select [Signature Code] From TaxRates

is correct. The error  "Too few parameters" normally is a result of wrong or mispelled  name of table or fields.

so, check them..
May be
AccessReader = AccessCmd.ExecuteReader
should be
AccessReader = AccessCmd.ExecuteReader()
Jacques Bourgeois (James Burger)Commented:
If you are just starting up with SQL Server and do not have much code written, drop the ODBC route. ODBC is an old technology from the end of the 80's (heydays of MS-DOS), that has been mostly replaced by OleDB in the middle of the 90's. Now, even OleDB is getting replaced. ODBC has been left in the framework only to support older systems or systems where the DBA is so stuck that he prevents anything to access the server more directly. Unless you are the victim of such a DBA, you do not help yourself by going through ODBC.

Since you are working with SQL Server, use the classes in the System.Data.SqlClient namespace: SqlConnection, SqlCommand,SqlDataReader and the likes. They are use the same way as the classes you have already started to use. In some instances, a simple Edit...Replace is sufficient to convert your application from one to the other.

The SQL classes are made explicitely for SQL Server. They are faster (sometimes a lot faster) and open up possibilities that ODBC simply does not have because it is a generic standard designed to work the same way with any database, thus preventing you from using some of the advanced features of many dabases.

The only drawback of using SQL over ODBC would be that should you decide to move to another database in the future, you will probably need a bigger code review than would be the case with ODBC. And if it is something that applies to your situation, use OleDB instead of ODBC. It is 10 years younger and offers a more direct link with the database.
mpdillonAuthor Commented:
Sometimes it just takes a new set of eyes. You are correct. I had the field name incorrect. And it was the first field name of 69 field names in this query. The one incorrect field name made the whole query fail, of course.
Thank you!
Jacques Bourgeois (James Burger)Commented:
By rereading your code, I seem to understand that you are writing an application only to move the structure and/or the data from Access to SQL Server. You do not have to do that. Access has a tool that will do that in a pinch, and will even convert some (usually not all) of your queries. You will find it with Tools...Database Utilities...Upsizing Wizard. The wizard will automatically make corrections acceptable in SQL Server for spaces and the such.

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now