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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

sakmsb

New Member
Joined
Apr 15, 2014
Messages
6
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
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
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
 

johnjones

New Member
Joined
Oct 6, 2014
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,309
Members
409,510
Latest member
HQ2401

This Week's Hot Topics

Top