How to execute an SQL insert/update from Excel for Mac

[Laphan]

Hi All

Basically I can use VBA/Macro to select and put the result data into a
worksheet no probs, but I have no idea how I post data (ie insert, update or
preferably send data to a stored proc).

My spec is I have 1 x Mac OS 9 clean and 1 x Mac OS 9 Classic connecting to
a Win2000 Server running SQL Server 2000. The client app is Excel 98 and
Excel 2001 and both are using the Merant ODBC 3.5.1 drivers that MS made
available on their web site when they forgot to include them on the Excel
2001 CD.

Can you help?

All I need is a simple VBA code snippet to show me how I do it.

Thanks

Laphan

 

[Jim Gordon MVP]

Hi Laphan,

Sub ExecuteStoredProcedure()
'
'
Dim Chan As Variant
'
'Open a Channel
'
Chan = SQLOpen("DSN=NT SQL Server")
'
'
'Execute the stored procedure "sp_who" and return the results to
'the active cell on the active sheeet.
'
Sheets("StoredProcedure").Select
SQLExecQuery Chan, "Execute sp_who"
SQLRetrieve Chan, ActiveCell
'
'Terminate the channel
'
SQLClose (Chan)
End Sub

In general, you can put any SQL statement into VBA. Here's an example using
the NorthWind database:

Sub RetrieveData()
'
Dim Chan As Variant
Sheets("RunQuery").Select
' Establish the connection to NWind
'
Chan = SQLOpen("DSN=NWind")

'
'Execute a query to retrieve the ORDER_ID and CUSTMR_ID
'from the Orders table where EMPLOYEE_ID is 555.
'
'
SQLExecQuery Chan, _
"SELECT Orders.Custmr_ID, Orders.Order_ID FROM Orders.dbf WHERE
orders.Employ_id='555'"
'
'
'Return the data to cell A5 on the active sheet
'
SQLRetrieve Chan, ActiveSheet.Range("A5"), , , True
'
'
'Close the connection
'
SQLClose (Chan)
'
End Sub

In your case you would use the syntax for SQL UPDATE rather than the SELECT
example shown. You can use any valid SQL statement within the VBA code
sample shown above.

-Jim

Hi All

Basically I can use VBA/Macro to select and put the result data into a
worksheet no probs, but I have no idea how I post data (ie insert, update or
preferably send data to a stored proc).

My spec is I have 1 x Mac OS 9 clean and 1 x Mac OS 9 Classic connecting to
a Win2000 Server running SQL Server 2000. The client app is Excel 98 and
Excel 2001 and both are using the Merant ODBC 3.5.1 drivers that MS made
available on their web site when they forgot to include them on the Excel
2001 CD.

Can you help?

All I need is a simple VBA code snippet to show me how I do it.

Thanks

Laphan

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info