excel vba to upload recordset to SQL

johnjones

New Member
Joined
Oct 6, 2014
Messages
4
Hi, i have an excel sheet that creates a new record in MS access via VBA code. works fine for the last 5 months
We have now moved to a microsoft SQL 2012 server, this takes away my access database.

I can not work out the code to capture the data on in the index sheet A2:Y2, then upload the data into the sql as a new record.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi, i have an excel sheet that creates a new record in MS access via VBA code. works fine for the last 5 months
We have now moved to a microsoft SQL 2012 server, this takes away my access database.

I can not work out the code to capture the data on in the index sheet A2:Y2, then upload the data into the sql as a new record.

have you edited the connection string?

there are awesome videos on this by wiseowltutorials, google it
 
Upvote 0
Show us your code, there's little we can do without it.

It should be fairly straightforward, you'll need to change the connection string and probably tweak the sql
 
Upvote 0
code used for ms access......
Sub gennum()
Dim acc As New Access.Application
Dim wb2 As Workbook
Dim usrname As String
Set wb2 = ThisWorkbook
Application.ScreenUpdating = False
acc.OpenCurrentDatabase "S:\TMS Quotes\TMS Quote.accdb"
With Worksheets("quote number")
.Activate
Range("D4").Select
Selection.ListObject.QueryTable.refresh BackgroundQuery:=False
Selection.Copy
End With
With Worksheets("summary")
.Activate
Range("D10").Select
ActiveSheet.Paste
End With
Application.CutCopyMode = False
usrname = Environ("username")
Range("usrname").Value = usrname

'acc.OpenCurrentDatabase "S:\TMS Quotes\TMS Quote.accdb"
acc.DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
"index", _
Application.ActiveWorkbook.FullName, _
True, _
"index$A1:y2"
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
booktosave
End Sub

code im trying to get to work for SQL...

Sub upload()
Dim cnn As ADODB.Connection
Dim strConnectionString As String
Dim sh As Worksheet
Dim strInsert As String
Dim lngRow As Long
Set sh = Sheets("index")
Set cnn = New ADODB.Connection
'strConnectionString = "Provider=SQLOLEDB.1;User ID=xx; password=xxxxxx!;Initial Catalog=xxxxx;Data Source=Wassim-xxxxxx;"
strConnectionString = "Provider=SQLOLEDB.1;Password=*********;Persist Security Info=True;User ID=******;Initial Catalog=TMSQuote;Data Source=******com.au;"


'Start
cnn.Open strConnectionString

strInsert = "INSERT INTO index (Qnumber, Margin) VALUES (" & -rs2.Field(Sheets("index").Range("a1")).Value & ", " & -rs2.Field(Sheets("index").Range("a2")).Value & ");"
Debug.Print strInsert
CurrentDb.Execute strInsert, dbFailonerror
'tried this code also............................
'For lngRow = 2 To LastUsedCell("d")
'strInsert = "INSERT INTO index ([Qnumber], [Margin], [Dis], [ValueEx]) " & _
' " VALUES (" & _
'sh.Cells(lngRow, 1) & ", " & _
'sh.Cells(lngRow, 2) & ", " & _
' sh.Cells(lngRow, 3) & ", " & _
' "'" & sh.Cells(lngRow, 4) & "')"

'" VALUES ([" & _
'sh.Cells(lngRow, 1) & "] , [" & _
' sh.Cells(lngRow, 2) & "] , [" & _
'sh.Cells(lngRow, 3) & "] , " & _
'"'" & sh.Cells(lngRow, 4) & "')"

' Debug.Print strInsert
'On Error Resume Next
'cnn.Execute strInsert
'Next lngRow
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top