Insert into SQL Server Database from ADO Access connection

[sugarflux]

Hi

So first off I wasn't quite sure whether I should post this in Access, SQL Server, ASP or another forum! I hope this is the right place...

I'm trying to do an automated daily import of around 640k records (originally in a csv file) into a SQL Server Database. Unfortunately the SQL DB is hosted and commands such as BULK INSERT and OPENROWSOURCE return permission denied errors interacting with SQL Server directly.

So my thinking is that if I start with a MS Access connection i should be able to insert from there into SQL Server?

So a heavily simplified version of my code (ASP) is as follows:

set con=Server.CreateObject("ADODB.Connection")
  con.open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyAccessDB.accdb;Mode=ReadWrite;Persist Security Info=False"
    
    sSQL="INSERT INTO [Provider=sqloledb;Data Source=db123.db.xxx.com,1433;Initial Catalog=db123;User Id=dbo123;Password=xxx;].[myTable] (Column1) SELECT 'HelloWorld' as Column1 "
    con.execute(sSQL)

  con.close
set con=nothing

Obviously not working at present - any ideas?

Thanks in advance,

~S~

 

[genomon]

Two things:
It would be easier to link the SQL Server table(s) into Access and use conventional SQL, either with a saved query or DoCmd.RunSQL.

If you are dealing with SQL Server 2012 or greater you will need to use an ODBC connection as OLEDB is no longer supported in this and subsequent versions.

Beir bua agus beannacht!

 

[lameid]

Haven't touched Access and SQL in about a year but assuming the rest is right...


sSQL="INSERT INTO myTable (Column1) SELECT 'HelloWorld' as Column1"

Your connecting direct to SQL, so you want to execute a native SQL Server command (some but not a lot of SQL differencs). All the connection information is done outside of the SQL statement.

There are tons of threads and probably FAQ's with examples.

But for a single record, I'd just link the table like genomon suggested.

 

[sugarflux]

Ok - firstly thanks very much for the replies! I dont think i was very clear....

The connection is an Access DB Connection rather than a SQL Server connection and this is because it's the only way i can actually connect to the CSV file.

Unfortunately I don't have the appropriate permission to link the SQL tables into the AccessDB as the SQL DB is hosted and quite locked down.

My example code should have actually looked like this:

set con=Server.CreateObject("ADODB.Connection")
  con.open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyAccessDB.accdb;Mode=ReadWrite;Persist Security Info=False"
    
    sSQL="INSERT INTO [Provider=sqloledb;Data Source=db123.db.xxx.com,1433;Initial Catalog=db123;User Id=dbo123;Password=xxx;].[myTable] (Column1) " & _
      "SELECT Column1 " & _
      "FROM [Text;HDR=Yes;FMT=Delimited;DATABASE=c:\myFolder].[myCSVFile.csv] csv "
    con.execute(sSQL)

  con.close
set con=nothing

I want to get a csv file into MS SQL database but the DB is hosted and permissions locked down preventing any type of BULK INSERT, OPENDATASOURCE etc. as well as linking the tables.

Thanks again,

~S~