MS Access and MS SQL Insert syntax

I would like to insert rows from a MS Access database table into a SQL 2008 database table. I am using Visual Studio 2008 (Visual Basic).

See the code below.
 
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 NoOfRecordUpdatedLong As Long = 0
Dim WSConn As New System.Data.SqlClient.SqlConnection
Dim WSCmd As New System.Data.SqlClient.SqlCommand
Dim WSReader As System.Data.SqlClient.SqlDataReader

ParmString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & FileNameString & ";Exclusive=1;Uid=admin;Pwd=;"
AccessConn.ConnectionString = ParmString
AccessConn.Open()
AccessCmd.Connection = AccessConn
Application.DoEvents()
        '
        ParmString = "Data Source=Dataserver;Initial Catalog=WeberScientific;" & _
                "User ID=reports;Password=reports"
WSConn.ConnectionString = ParmString
WSConn.Open()
WSCmd.Connection = WSConn
Application.DoEvents()
        '
'Both Data connections are open 
ParmString = "Truncate table dbo.TaxMORate"
WSCmd.CommandText = ParmString
NoOfRecordUpdatedLong = WSCmd.ExecuteNonQuery
Application.DoEvents()
'
'Code runs fine to here!
ParmString = "INSERT INTO [WeberScientific].[dbo].[TaxMORate ] " & _
"([C_StateCode]) " & _
"Select [State Code] from TaxRates where [State Code] = 'MO'"
AccessCmd.CommandText = ParmString
NoOfRecordUpdatedLong = AccessCmd.ExecuteNonQuery
'THe ExecuteNonQuery fails.
'"Syntax Error Insert Into"

Open in new window


This code errors out. Could the problem be that the Access database is not specifically referenced in the Insert statement. I did reference the SQL database as I normailly would. But I do not know how to combine Access and SQL in the same statement.

Thanks,
pat
mpdillonAsked:
Who is Participating?
 
LowfatspreadCommented:
you need to define the access database as a linked server on the ms sql server , and just have the sql server connection in your code...

then you can run the insert on the sql server...

insert into
  select ... from ...

you could possibly use openquery/rowset to reference the ms access database rather than the "static" linked server approach...

alternatively define that sql server tables as linked tables in the access database and perform the insert via that connection...
0
 
bigeven2002Commented:
Hello,

Your syntax looks correct, but looking at line 30, is there supposed to be a space after TaxMoRate before the closing bracket?
0
 
mpdillonAuthor Commented:
I did not notice the space. I have removed it, however the error continues.

INSERT INTO [WeberScientific].[dbo].[TaxMORate] ([C_StateCode])  Select [State Code] from TaxRates where [State Code] = 'MO'
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
bigeven2002Commented:
Ok I wonder if it is the brackets it does not like.  Try this:

INSERT INTO [WeberScientific].[dbo].[TaxMORate] ([C_StateCode])  
VALUES (Select [State Code] from TaxRates where [State Code] = 'MO')

Open in new window


You might also try it with TaxRates enclosed in brackets: [TaxRates]
0
 
mpdillonAuthor Commented:
Thanks but that didn't work either. I have question about the key word VALUES. Isn't it left our when there is  a SELECT statement? Or am I remembering that incorrectly?
0
 
mpdillonAuthor Commented:
Lowfatspread,
This program will have to import several sales tax access database each month. So it seems like I would have to create a new linked server for each database, each month. is that correct?
I am not sure the user doing this would want to do that.
I guess I could always read each Access record and build an Insert statement for each Access record. It would be slower but much more simple for the client.
Thoughts?
pat
0
 
bigeven2002Commented:
The VALUES method is another way of doing it, it has the same effect.  The only difference is you would normally use the VALUES method if inserting into more than one row, but your code inserts into only one row so both ways would be equal in this case.  I just thought that might take care of the syntax error.
0
 
LowfatspreadCommented:
are you saying that a new access database is created each month?

please tell us more background on the actual requirement....

you maybe better off taking a step back and looking at how the whole process should be controlled..
(e.g. first copy the access data into a standard input data(base/area) and then import....)

or have you considered SSIS packages or using IMPORT/BCP (Bulk load) ?
0
 
plummetCommented:
I think the following syntax should work. You only need square brackets around something that would not be recognised as a name otherwise, ie a field name that contains a space or similar.


INSERT INTO WeberScientific.dbo.TaxMORate (C_StateCode)  
Select [State Code] from TaxRates where [State Code] = 'MO'

Open in new window


I hope that helps
0
 
mpdillonAuthor Commented:
Lowfatspread,
Every month the client receives an email with several new Access databases. The objective of my program is to empty out the exsiting SQL tax tables and replace them with the information contained in the new Access databases.
I was hoping to just do an INSERT as I would do if I were trying to insert multiple rows from one SQL table into another SQL table. The INSERT statement I have written does not work as I have written it.
Last night I did rewrite my program to read each row of the Access database table. I place the field values into a Insert statement and execute that statement. Then I read and process the next row. e.g. Insert Into TaxMORate (C_StateCode) values ('MO').
Of course the actual Insert contains many more fields, 69 in total. And the Access database table contains duplicate rows which necesitates using a Group by to eliminate the duplicates. There are only about 2000 unique rows that I need to import. So my brute force method works and does not take too much time, a few seconds.
I ran into two problems with my new INSERT. The GETFIELDTPYE(i) does not distinguish between a VARCHAR and a SMALLDATETIME. Both are identified as a SMALLDATETIME. And the Access smalldatetime values are returned in a format of "YYYY/MM/DD". My Insert would not insert a date in this format particularly when its value was 0000/00/00. It took some work but I identified the date fields and programmed around these limitations.
That is all I can think of to tell you.
Plummet,
I am off to work will test your code this evening.

Thanks,
pat
0
 
mpdillonAuthor Commented:
Error in last post. Both VARCHAR and SMALLDATETIME are identified as VARCHAR not smalldatetime.sorry.
0
 
Anthony PerkinsCommented:
Any chance you can share with us the current code you are using and corresponding error message generated?
0
 
mpdillonAuthor Commented:
acperkins,
I have attached a screen shot of two errors. In one error the database is enclosed in [ ] and the other error image shows the database without the square brackets. The dimemsioning of the connections is shown in the code at the top of this post.
 Error 1 With square brackets
0
 
plummetCommented:
Are you trying to insert from an Access table into a SQL server one? If you are then you will either have to use linked SQL tables in your Access DB or you'll have to load the data from Access into memory and then write it to SQL server...
0
 
Anthony PerkinsCommented:
The syntax error you are getting is because you are attempting to insert into a SQL Server table using SQL Server syntax with an MS Access provider.  Either:
1. Link the MS Access table to your MS Access database and correct the syntax or
2. Use a SQL Server provider.
0
 
mpdillonAuthor Commented:
Thanks guys, I was hoping to do a direct insert but you have confirmed that will not work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.