Public Con As New ADODB.Connection
Public Function Establish_Connection()
Dim ConnectionString As String
ConnectionString = "Provider=SQLOLEDB;Data Source=PAVAN-PC;Initial Catalog=Excel_Access_SQL;Integrated Security=SSPI"
Con.Open ConnectionString
End Function

Public Function Close_the_Connection()
Con.Close
End Function

Sub ConnectToDatabase_CreateTable()
Establish_Connection
Con.Execute "Create table Marks (StudentName varchar (15),RollNo int, Class int, Science int, Social int, Maths int, GK int)"
MsgBox ("Hi Table Created")
Close_the_Connection
End Sub

Sub Insert_Data_Into_SQL_Access_Excel()
Application.DisplayAlerts = False
Establish_Connection
SName = UserForm1.TextBox1.Value
RollNo = UserForm1.TextBox2.Value
Class = UserForm1.TextBox3.Value
Science = UserForm1.TextBox4.Value
Social = UserForm1.TextBox5.Value
Maths = UserForm1.TextBox6.Value
GK = UserForm1.TextBox7.Value
'MsgBox SName & "," & RollNo & "," & Class & "," & Science & "," & Social & "," & Maths & "," & GK
Con.Execute "insert into Marks values ('" & SName & "', " & RollNo & ", " & Class & ", " & Science & ", " & Social & "," & Maths & ", " & GK & ")"
Close_the_Connection
MsgBox ("Hi Inserted the values into the SQL Table")
'=============Insert Data into Access Database====
'=====================================
Dim FileName As String
FileName = Application.GetOpenFilename()
'MsgBox FileName
Con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & FileName & ";" & _
"User Id=admin;Password="
Con.Open
Dim rs As New ADODB.Recordset
With rs
.ActiveConnection = Con
.LockType = adLockOptimistic 'Unlock the table to update the records
.Open "Marks"
End With
With rs
.AddNew
.Fields("Student Name") = UserForm1.TextBox1.Value
.Fields("Roll No") = UserForm1.TextBox2.Value
.Fields("Class") = UserForm1.TextBox3.Value
.Fields("Science") = UserForm1.TextBox4.Value
.Fields("Social") = UserForm1.TextBox5.Value
.Fields("Maths") = UserForm1.TextBox6.Value
.Fields("GK") = UserForm1.TextBox7.Value
.Update
End With
rs.Close
Con.Close
Set rs = Nothing
Set cn = Nothing
MsgBox ("Hi Inserted the values into the Access Database")

'=============Insert Data into Excel==========
'=====================================

FileName = Application.GetOpenFilename()
'MsgBox FileName
Workbooks.Open (FileName)
Dim WKB As Workbook
Set WKB = ActiveWorkbook
Dim SH As Worksheet
Set SH = WKB.Sheets("Sheet1")
LastRow = SH.Range("A" & Rows.Count).End(xlUp).Row + 1
SH.Range("A" & LastRow) = UserForm1.TextBox1.Value
SH.Range("B" & LastRow) = UserForm1.TextBox2.Value
SH.Range("C" & LastRow) = UserForm1.TextBox3.Value
SH.Range("D" & LastRow) = UserForm1.TextBox4.Value
SH.Range("E" & LastRow) = UserForm1.TextBox5.Value
SH.Range("F" & LastRow) = UserForm1.TextBox6.Value
SH.Range("G" & LastRow) = UserForm1.TextBox7.Value
Unload UserForm1
WKB.Save
WKB.Close
MsgBox ("Hi Inserted the values into Excel workbook")
Application.DisplayAlerts = True
End Sub