Automate SQL Server using VBA

Looking to upskill yourself?

In this friendly/easy to follow video series you'll learn how to integrate Excel and SQL Server using VBA from your Excel. You'll learn how to Create Database, Tables, Fields, understand Data types, Use VBA to sync/export/import, write/update/delete records using VBA, Link Excel to SQL Server and many more. Enjoy!

Click here for all videos 

Subscribe Now!
New videos every Weekend!

Like what I do? Donate

Did I help you? Did one of my tutorials save you sometime? 

You can say thank you by buying me a cup of coffee, I go through a lot of it.

Help keep Greater Good resources free for everyone. Please donate today. 

This page is not monitored so for questions please comment on the youtube video page. For suggestions email vbaa2z.team@gmail.com

VBA and SQL Server - How to create connection to SQL Server Database from Excel. Part-8

Hello friends, all relevant materials for this topic/tutorial can be downloaded from here. Please support us by subscribing to our channel and sharing them with your friends.

If you have any questions/feedback/tutorial request, please you can email me directly vbaa2z.team@gmail.com or comment on YouTube Video (blog comments are not actively monitored).

https://www.youtube.com/watch?v=FNNQturTjX8&lc

Option Explicit

Sub sync_data_vba_sqlserver()

'-----------------------------
'Thanks for downloading the code. 
'Please visit our channel for a quick explainer on how to use this code.
'Feel free to update the code as per your need and also share with your friends.
'Download free codes from http://vbaa2z.blogspot.com
'Support our channel: youtube.com/vbaa2z
'Author: L Pamai (vbaa2z.team@gmail.com)
'-----------------------------

Dim conn As ADODB.Connection
Dim cs As String
Dim SQLstr As String
Dim x As Long
Dim fld As ADODB.Field

On Error GoTo errrrrr

Set conn = New ADODB.Connection
cs = "DRIVER=SQL Server;"
cs = cs & "DATABASE=TransacDb;"
cs = cs & "SERVER=LP-PC"

'Data Source=SQL Server;Initial Catalog=LP-PC;Persist Security Info=True;User ID=;Password=


conn.Open cs, "", ""

Dim rst As New ADODB.Recordset
Set rst = New ADODB.Recordset

SQLstr = "Select * From FlowTb;" ' WHERE county = 'Orleans';"

rst.Open SQLstr, conn, adOpenForwardOnly, adLockReadOnly

x = 0

Sheets("Sheet2").Select

For Each fld In rst.Fields
    Cells(1, x + 1).Value = fld.Name
    x = x + 1
Next fld

Range("A2").CopyFromRecordset rst
[a1] = Now()

rst.Close
Set rst = Nothing

conn.Close
Set conn = Nothing

Exit Sub
errrrrr:

Debug.Print Err.Number & ". " & Err.Description
conn.Close
Set conn = Nothing

End Sub

Create Table in SQL Server using VBA

Create Table in SQL Server using VBA

Option Explicit


Sub create_SQL_Server_DB_tb()

'-----------------------------
'Thanks for downloading the code. 
'Please visit our channel for a quick explainer on this code.
'Feel free to update the code as per your need and also share with your friends.
'Channel: Youtube.com/vbaa2z
'Download free codes from http://vbaa2z.blogspot.com
'Autor: L Pamai (vbaa2z.team@gmail.com)
'-----------------------------

Dim conn As ADODB.Connection
Dim cs As String
Dim sqlcmd As String

On Error GoTo errrrrr
Set conn = New ADODB.Connection
cs = "DRIVER=SQL Server;"
cs = cs & "DATABASE=LPSQLSERVER;"
cs = cs & "SERVER=LP-PC"

conn.Open cs, "", ""

sqlcmd = "CREATE TABLE VBAA2ZdemoTb(prim_id INT IDENTITY(1,1) PRIMARY KEY,email_id Varchar(255),LastName varchar(255),FirstName varchar(255),Address varchar(255),CityCode INT, Amt_Inc Money);"

conn.Execute sqlcmd
conn.Close

Set conn = Nothing

Exit Sub
errrrrr:

Debug.Print Err.Number & ". " & Err.Description
conn.Close
Set conn = Nothing

End Sub