DB Functions (By AI)

3 functions to add or update data to DB and Read from DB.
These were also generated by Bing AI, so need work to convert to proper functions.

SaveRangeToDB: Inserts range (as table) from spreadsheet to SQL table.
UpdateTableFromDB: Updates SQL table with records from spreadhseet table using 1st column as ID.
GetDataFromADO: Reads table from SQL DB into spreadhseet.

CodeFunctionName
What is this?

Public

Not Tested

Imported
Sub UpdateTableFromDB ()
    ' Alright, I can try to write another macro for you. One way to update a table in spreadsheet with a SQL table using the first column as index is to use the UPDATE statement and loop through the rows in the table. Here is an example of such a macro:
    ' You need to modify the table name, the connection details, the range and the number of columns according to your needs.
    ' You also need to make sure that the order and type of the values match the columns in your table.
    ' You can find more details and examples on how to update data from Excel to SQL Server using VBA in these links.
    ' Source: Conversation with Bing, 4/18/2023
    ' (1) Update SQL Server table from Excel VBA - Stack Overflow. https://stackoverflow.com/questions/22229765/update-sql-server-table-from-excel-vba.
    ' (2) Update SQL from Excel sheet using VBA - Stack Overflow. https://stackoverflow.com/questions/52565661/update-sql-from-excel-sheet-using-vba.
    ' (3) How to Update a SQL Table from Excel | SQL Spreads. https://sqlspreads.com/blog/how-to-update-an-sql-table-from-excel/.
    ' (4) Insert, update, and delete records from a table using Access SQL. https://learn.microsoft.com/en-us/office/vba/access/concepts/structured-query-language/insert-update-and-delete-records-from-a-table-using-access-sql.
    Const Tbl As String = "YourTableName" ' Enter your table name here
    Dim UpdateQuery As String, xlRow As Long, xlCol As Integer
    Dim DBconnection As Object
    Set DBconnection = CreateObject ("ADODB.Connection")
    DBconnection.Open "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUserID;Password=YourPassword;" ' Enter your connection details here
    UpdateQuery = ""
    xlRow = 2 ' The row where your data starts
    xlCol = 1 ' The column where your data starts
    While Cells (xlRow, xlCol) < > ""
        UpdateQuery = "UPDATE " & Tbl & " SET "
        For xlCol = 2 To 110 ' The number of columns in your data
            UpdateQuery = UpdateQuery & Cells (1, xlCol) & " = '" & Replace (Cells (xlRow, xlCol), "'", "''") & "', " ' Includes mitigation for apostrophes in the data
        Next xlCol
        UpdateQuery = Left (UpdateQuery, Len (UpdateQuery) - 2) & " WHERE " & Cells (1, 1) & " = '" & Cells (xlRow, 1) & "'" & vbCrLf ' Use the first column as index
        xlRow = xlRow + 1 ' Move to the next row
        DBconnection.Execute UpdateQuery 'Execute the query
    Wend
    DBconnection.Close 'Close the connection
    Set DBconnection = Nothing
End Sub
Sub SaveRangeToDB ()
    ' Okay, I can try to write a different macro for you. Another way to save a range of Excel spreadsheet back into a database table is to use the INSERT INTO statement and loop through the cells in the range. Here is an example of such a macro:
    ' You need to modify the table name, the connection details, the range and the number of columns according to your needs.
    ' You also need to make sure that the order and type of the values match the columns in your table.
    ' You can find more details and examples on how to insert data from Excel to SQL Server using VBA in these links.
    ' Source: Conversation with Bing, 4/18/2023
    ' (1) Push Excel Range to SQL Table via VBA - Stack Overflow. https://stackoverflow.com/questions/58845318/push-excel-range-to-sql-table-via-vba.
    ' (2) INSERT INTO statement from Excel to SQL Server Table using VBA. https://stackoverflow.com/questions/52897825/insert-into-statement-from-excel-to-sql-server-table-using-vba.
    ' (3) Import Excel Sheet into SQL Server Table - mssqltips.com. https://www.mssqltips.com/sqlservertip/7205/import-excel-sheet-into-sql-server-table/.
    ' (4) Excel-SQL Server Import-Export using VBA. https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm.
    Const Tbl As String = "YourTableName" ' Enter your table name here
    Dim InsertQuery As String, xlRow As Long, xlCol As Integer
    Dim DBconnection As Object
    Set DBconnection = CreateObject ("ADODB.Connection")
    DBconnection.Open "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUserID;Password=YourPassword;" ' Enter your connection details here
    InsertQuery = ""
    xlRow = 1 ' The row where your data starts
    xlCol = 1 ' The column where your data starts
    While Cells (xlRow, xlCol) < > ""
        InsertQuery = InsertQuery & "INSERT INTO " & Tbl & " VALUES ('"
        For xlCol = 1 To 110 ' The number of columns in your data
            InsertQuery = InsertQuery & Replace (Cells (xlRow, xlCol), "'", "''") & "', '" ' Includes mitigation for apostrophes in the data
        Next xlCol
        InsertQuery = Left (InsertQuery, Len (InsertQuery) - 3) & ")" & vbCrLf ' Remove the last comma and close the parenthesis
        xlRow = xlRow + 1 ' Move to the next row
    Wend
    DBconnection.Execute InsertQuery ' Execute the query
    DBconnection.Close ' Close the connection
    Set DBconnection = Nothing
End Sub
Sub GetDataFromADO ()
    ' Sure, I can help you with that.
    ' There are different ways to create an Excel macro to read data from a SQL database into a sheet.
    ' One way is to use the ADODB library and connection objects to execute a SQL query and copy the results to a range. Here is an example of such a macro:
    ' You need to modify the connection string, the SQL query and the range according to your needs.
    ' You also need to add a reference to Microsoft ActiveX Data Objects library in the VBA editor.
    ' You can find more details and examples on how to connect and pull data from SQL Server database in Excel using VBA in these links.
    ' Source: Conversation with Bing, 4/18/2023
    ' (1) Accessing SQL Database in Excel-VBA - Stack Overflow. https://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba.
    ' (2) How to extract the data from SQL Server to Excel using vba?. https://stackoverflow.com/questions/46412326/how-to-extract-the-data-from-sql-server-to-excel-using-vba.
    ' (3) Connect and Pull data from SQL Server database in Excel using VBA. https://www.encodedna.com/excel/connect-and-pull-data-from-sql-server-in-excel-using-vba.htm.
    ' (4) Retrieve Data From A Database To Excel Using SQL - Excelguru. https://excelguru.ca/retrieve-data-from-a-database-to-excel-using-sql/.
    ' (5) ADO Excel VBA - SQL Connecting to Database Example Macros - Analysistabs. https://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/.
    'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    Dim strSQL As String
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;" ' Open Connection'
    objMyConn.Open
    strSQL = "select * from myTable" ' Set and Excecute SQL Command'
    Set objMyRecordset.ActiveConnection = objMyConn ' Open Recordset'
    objMyRecordset.Open strSQL
    ActiveSheet.Range ("A1").CopyFromRecordset (objMyRecordset) ' Copy Data to Excel'
End Sub

Views 1,380

Downloads 80

CodeID
DB ID