How can I get VBA to create a table or run a query in MS SQL?

[Rubble]

I use Excel quite a bit using ODBC connections with Access (where queries are
stored) that ultimately ends up querying a MS SQL database -- What I would
like to do is eliminate the "middle guy" in not using Access in between and
would also like to create dynamic queries within Excel's vba environment to
query the MS SQL database directly -- I would also like to create dynamic
queries in vba -- I can handle the dynamic query part of creating a sql query
-- I just don't know how to handle sending the query directly to MS SQL from
VBA.

Any ideas?

I have been successful in creating select queries from a vba environment --
its just trying to do make table queries withing vba --

 

[Duke Carey]

MSDN has any number of articles about how to do this. Here's one of them

http://support.microsoft.com/kb/278973

 

[Duke Carey]

Probably a better article to start

http://office.microsoft.com/en-us/assistance/HA010548031033.aspx

 

[Rubble]

Thank you for your replies -- it looks like after a quick look at the two
articles that both address how to pull data into Excel from an external
database -- do either articles indirectly address how to write data from
Excel to the MS SQL database? I am thinking I may have to just write a
procedure that saves a csv file to my hard drive in vba and then create
something on the MS SQL side to import the file.

Once again -- thank you for your help on this !!

 

[Rubble]

K - I think I got it now -- I use

strQuery = "SELECT * FROM [Sheet1$A1:B10]"

in order to get the information that I want to update into MS SQL -- and
then after identifying the SQL database and all I use something like the
following :

sSQL = "INSERT INTO Contacts (FirstName, LastName) " & _
" VALUES (strQuery)"
oConn.Execute sSQL

Is that at least going down the correct road?

Thanks Again

 

[Duke Carey]

That's the path I'd go down.

 

[Rubble]

Thank you