load access table to sql

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Andre (no_at_spam.com)
Date: 08/26/04 

Date: Wed, 25 Aug 2004 22:30:46 -0700

I'm trying to load an Access table to SQL 2k. I'm connecting to SQL just
fine (using ado), and emptying the SQL table just fine. But when I try to
do the following, nothing makes it to SQL:

    strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
             "SELECT LineOfBusiness, Provider, [Group], [SortOrder#],
Region, SiteIdx, Site, MM, [Month], [Year] FROM Enrollment"
    cn.Execute strSQL

Obviously, dbo.Enrollment is the SQL table, and Enrollment is the local
Access table. The statement executes just fine, but like I say, no data
makes it to the table. I tried the following, and sure enough, 1 row made
it in:

    strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
             "SELECT '1', '1', '1', '1', '1', '1', '1', '1', 1, 2004 "

So it must be something in my Select, right?

Any help would be greatly appreciated.

Thanks, Andre

Relevant Pages