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

R

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 --
 
R

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 !!
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top