ODBC connection for insert into SQL Server
 

I'm able to do the following to SELECT from a SQL Server:

--

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=myServer;UID=;APP=Microsoft Office
XP;WSID=myWorkstation;Trusted_Connection=Yes" _
, Destination:=newsheet.Range("A1"))
.CommandText = Array( _
"SELECT myData FROM myTable")

---snip ---

End With

--

I did this easily enough through Data/Import External Data.

How do I establish the same connection in order to do an INSERT into
the SQL Server? Can I do this through an ODBC connection?

For some reason I'm not able to create an ADODB.Connection object. I
do have Microsoft ADO Ext 2.7 added as a resource. Is there another
resource that I need? If so, how do I get it?

Thanks,

NS

ODBC connection for insert into SQL Server
 

This might be a help for getting data to and from Excel and Access. It's
easily adaptable for SQL Server.

It includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"nevada" wrote in message
om...
I'm able to do the following to SELECT from a SQL Server:

--

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=myServer;UID=;APP=Microsoft Office
XP;WSID=myWorkstation;Trusted_Connection=Yes" _
, Destination:=newsheet.Range("A1"))
.CommandText = Array( _
"SELECT myData FROM myTable")

---snip ---

End With

--

I did this easily enough through Data/Import External Data.

How do I establish the same connection in order to do an INSERT into
the SQL Server? Can I do this through an ODBC connection?

For some reason I'm not able to create an ADODB.Connection object. I
do have Microsoft ADO Ext 2.7 added as a resource. Is there another
resource that I need? If so, how do I get it?

Thanks,

NS