Search Results

Search found 4 results on 1 pages for 'user307655'.

Page 1/1 | 1 

• Excel VBA to Update SQL Table

  - by user307655

  Hi All, I have a small excel program that is use to upload data to an SQL server. This has been working well for a while. My problem now is that I would like to offer to users a function to update an existing record in SQL. As each row on this table has a unique id columne. There is a column call UID which is the primary key. This is part of the code currently to upload new data: Set Cn = New ADODB.Connection Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _ ";Uid=" & UserID & ";Pwd=" & Password & ";" rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic For RowCounter = StartRow To EndRow rs.AddNew For ColCounter = 1 To NoOfFields rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter) Next ColCounter Next RowCounter rs.UpdateBatch ' Tidy up rs.Close Set rs = Nothing Cn.Close Set Cn = Nothing Is there anyway i can modify this code to update a particular UID rather than importing new records? Thanks again for your help

  Read the article

• Excel VBA SQL Import

  - by user307655

  Hi All, I have the following code which imports data from a spreadsheet to SQL directly from Excel VBA. The code works great. However I am wondering if somebody can help me modify the code to: 1) Check if data from column A already exists in the SQL Table 2) If exists, then only update rather than import as a new role 3) if does not exist then import as a new role. Thanks again for your help Sub SQLIM() ' Send data to SQL Server ' This code loads data from an Excel Worksheet to an SQL Server Table ' Data should start in column A and should be in the same order as the server table ' Autonumber fields should NOT be included' ' FOR THIS CODE TO WORK ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library Dim Cn As ADODB.Connection Dim ServerName As String Dim DatabaseName As String Dim TableName As String Dim UserID As String Dim Password As String Dim rs As ADODB.Recordset Dim RowCounter As Long Dim ColCounter As Integer Dim NoOfFields As Integer Dim StartRow As Long Dim EndRow As Long Dim shtSheetToWork As Worksheet Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1") Set rs = New ADODB.Recordset ServerName = "WIN764X\sqlexpress" ' Enter your server name here DatabaseName = "two28it" ' Enter your database name here TableName = "COS" ' Enter your Table name here UserID = "" ' Enter your user ID here ' (Leave ID and Password blank if using windows Authentification") Password = "" ' Enter your password here NoOfFields = 7 ' Enter number of fields to update (eg. columns in your worksheet) StartRow = 2 ' Enter row in sheet to start reading records EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row ' Enter row of last record in sheet ' CHANGES ' Dim shtSheetToWork As Worksheet ' Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1") '** Set Cn = New ADODB.Connection Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _ ";Uid=" & UserID & ";Pwd=" & Password & ";" rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic For RowCounter = StartRow To EndRow rs.AddNew For ColCounter = 1 To NoOfFields rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter) Next ColCounter Next RowCounter rs.UpdateBatch ' Tidy up rs.Close Set rs = Nothing Cn.Close Set Cn = Nothing End Sub

  Read the article

• Using Excel VBA to Create SQL Tables

  - by user307655

  Hi All, I am trying to use Excel VBA to automate the creation of a SQL table in an existing SQL Database. I have come across the following code on this side. Private Sub CreateDatabaseFromExcel() Dim dbConnectStr As String Dim Catalog As Object Dim cnt As ADODB.Connection Dim dbPath As String Dim tblName As String 'Set database name in the Excel Sheet dbPath = ActiveSheet.Range("B1").Value 'Database Name tblName = ActiveSheet.Range("B2").Value 'Table Name dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";" 'Create new database using name entered in Excel Cell ("B1") Set Catalog = CreateObject("ADOX.Catalog") Catalog.Create dbConnectStr Set Catalog = Nothing 'Connect to database and insert a new table Set cnt = New ADODB.Connection With cnt .Open dbConnectStr .Execute "CREATE TABLE tblName ([BankName] text(50) WITH Compression, " & _ "[RTNumber] text(9) WITH Compression, " & _ "[AccountNumber] text(10) WITH Compression, " & _ "[Address] text(150) WITH Compression, " & _ "[City] text(50) WITH Compression, " & _ "[ProvinceState] text(2) WITH Compression, " & _ "[Postal] text(6) WITH Compression, " & _ "[AccountAmount] decimal(6))" End With Set cnt = Nothing End Sub However i can't successfully get it to work? What I am trying to do is actually use Excel to create a table not a database? The database already exists. I would just like to create a new table. The name of the table will be referenced from cell A1 in Sheet 1. Can somebody please help. Thanks Regards Gerard

  Read the article

• Excel VBA SQL Data

  - by user307655

  Hi All, I have a small excel program. I would like to be able to use this program to update a SQL table. What would be the function to say update line 2 in SQL table Test in Database ABC Thanks

  Read the article

1