MS Access Select statement

Posted on 2011-10-23
Last Modified: 2012-05-12
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?

Question by:mpdillon
    LVL 119

    Accepted Solution

    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..
    LVL 39

    Expert Comment

    May be
    AccessReader = AccessCmd.ExecuteReader
    should be
    AccessReader = AccessCmd.ExecuteReader()
    LVL 40

    Expert Comment

    by:Jacques Bourgeois (James Burger)
    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.

    Author Closing Comment

    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!
    LVL 40

    Expert Comment

    by:Jacques Bourgeois (James Burger)
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now