Write to Microsoft SQL db from Excel VBA

[sgremmo]

Hi, I need add a new record into a table of db (db of Microsoft SQL).

I'm able to prepare data in vba excel and read table from db.

I don't know the steps to connect db and write into it.

Thank for help.

Simone

 

[Norie]

Simone

How are you reading from the table?

If you can do that you must be making some sort of connection to it.

 

[sgremmo]

I use an italian version ogf excel.

In Excel from menu "Data", "From other origin?" (icon n.4) , From SQL Server open wizard data connection.

After can read new data from db clicing on update all (icon n. 5).

In this way can retrieve data from db into excel.
If I can use this connection to write into db from excel I don't know to do it.

Thanks.

 

[obiron]

Generally it is unwise to put data back into SQL from Excel for a number of reasons which are not really relevant here.

It is safer to get SQL to read the data using the OPENROWSOURCE command or to create a linked server to the Excel spreadsheet, but this will require the feature to be enabled (surface area configuration) in SQL (you don't say which version of SQL you are using)

If you really must post the data back from Excel then you will need to follow these steps

Create a new ADODB connection
provide the connection string (server instance, database, user and password depending on how SQL authentication is configured)
prepare an SQL statement
Execute the statement.
Trap for any errors that may occur (e.g. failure to connect to db, failure to post the data because of data type or referential integrity checks, transaction rollback etc...)

It is extremely dangerous to hand prepare SQL as the change of making an error is high and it leaves the database vunerable to an SQL injection attack. If you really must do this, please consider writing a stored procedure in SQL that takes parameters and executing the SP. but this is a wholly different discussion better suited to an SQL board.

Obiron

 

[sgremmo]

Obiron, thank you for your explanation. I think that every item are right!
Please consider that I nedd to write in a db on a server from 15/20 PC in an intranet. So I think to dont' have particoular security problems.

Can you explain this step:

Create a new ADODB connection -- is the same that i already use to read data from db into excel?

provide the connection string (server instance, database, user and password depending on how SQL authentication is configured) -- Can have an example of sintax.

Thank you for help.

 

[Norie]

Simone

It's actually quite straightforward to write to SQL Server using ADO, if you've got all the permissions of course.

Here's some very simple code to get a connection:

Private Sub XLToADO()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSRV As String    
Dim strDB As String

    ' connect to SQL Server

    strSRV = "MySERVER"
    strDB = "dboCustomers"
    
    Set conn = New ADODB.Connection
    
    ' create connection string
    strConn = "Driver={SQL Server};Server=" & strSRV & ";Database=" & strDB"
    
    conn.ConnectionString = strConn
    
    conn.Open

    strConn = strConn & "Provider=SQLOLEDB;Data Source=" &strSRV & ";"
    strConn = strConn & "Initial Catolog=" & strDB & ";Trusted_Connection=YES"

A password can be included in the connection string.

Actually looking at those they aren't very good exaamples, take a look here instead:

ConnectionStrings

Once you've got the connection you need to create the SQL for whatever you are doing and then execute it.