+ Reply to Thread
Results 1 to 8 of 8

VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    17

    VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    Hi All,

    I am quite anxious to know how SQL can be used in VBA to transfer data programatically to an Access Database or a SQL Server Database. I find this to be quite an interesting area of ADO and Excel but I could not find resources online to accomplish the task I have in mind.

    The requirement:

    I need to be able to transfer data to an existing access database or a SQL server database which has predefined columns/field names. From excel I would like to read row wise and take specific cell values into variable(s) which I can then use in a SQL statement and inject into the database. During this operation if a duplicate value (i.e. primary key) is encountered then i want that cell to be highlighed in red so that the user can check why and then either ignore or correct and re-upload.

    I know that there is an approach using recordset, which is what I can see the most online but I can't see examples where SQL statements are used and executed. I also need to know how people obtain the connection strings? Is there a logic or somewhere in Excel where this can be found? I know I can take it off the internet, but I'd like to know how this was obtained as i've observed that the connection strings change with the version of excel or access.

    I'd also like an explanation (if possible) on what is being done when using the recordset method or SQL method

    I would appreciate all the help in this regard.

    Steve

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    www.connectionstrings.com

    http://en.wikipedia.org/wiki/ActiveX_Data_Objects

    http://support.microsoft.com/kb/246335

    http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

  3. #3
    Registered User
    Join Date
    05-14-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    Hi

    Some of the links are quite helpful with regards to ADO.NET

    The link about recordset shows an example to copy from a database to excel, I want it the other way around excel to either access database or sql server

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    You don't really give any specifics but to do what you want you should probably be looking at recordsets and commands.

    Also, if you want to put a lot of data into a database then it might be possible to do it with some sort of 'bulk' operation.

    That's kind of database specific though.

    Here's an example of exporting data from an entire sheet to SQL Server.

    It actually shows 3 different methods.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    05-14-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    Hi Norie,

    While waiting for a solution i ended up writing the below code. Not sure how it differs from the approach you have suggested above. What I would like to know is what are the different approaches, its differences etc. In addition, how can I get more knowledge and familiarity with ADO because what you have suggested above looks different.

    The below code has an issue with the on Error Goto statement. After one iteration, it does not go to the error label and do the task required. Any idea why?

    Public Sub Bulk_Uploader()

    Dim adocon As New ADODB.Connection
    Dim adoRS As New ADODB.Recordset
    Dim sqlcomm As New ADODB.Command
    Dim strSQL As String
    Dim strConString As String


    Dim strUR As String
    Dim Created_date As Date
    Dim strMerchantID As Double
    Dim strMerchantName As String
    Dim strOrderNumber As String
    Dim strProductID As String
    Dim strRefundAmt As String
    Dim strRefNumber As String
    Dim strComments As String

    strConString = "Microsoft.ACE.OLEDB.12.0,C:\Users\Steve Babu\Desktop\Manual_RefundsDB.accdb"

    ThisWorkbook.Worksheets("Sheet1").Range("A2").Select



    With adocon
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open "C:\RefundsDB.accdb"
    End With

    Do While ActiveCell.Value <> ""

    Created_date = Now
    strMerchantID = ActiveCell.Value
    strMerchantName = ActiveCell.Offset(0, 1).Value
    strOrderNumber = ActiveCell.Offset(0, 2).Value
    strRefundAmt = ActiveCell.Offset(0, 3).Value
    strProductID = ActiveCell.Offset(0, 4).Value
    strRefNumber = ActiveCell.Offset(0, 5).Value
    strComments = ActiveCell.Offset(0, 6).Value
    strUR = strMerchantID & strOrderNumber & strProductID

    strSQL = "INSERT INTO REFUNDS (UR,CREATED_DATE,Merchant_ID,Merchant_NAME,ORDER_NUMBER,PRODUCT_ID,REFUND_AMOUNT," & _
    "REFERENCE_NUMBER,COMMENTS) VALUES('" & strUR & "','" & Created_date & "','" & strMerchantID & "','" & strMerchantName & "','" & _
    strOrderNumber & "','" & strProductID & "','" & strRefundAmt & "','" & strRefNumber & "','" & strComments & "')"


    On Error GoTo a:
    adoRS.CursorLocation = adUseServer
    adoRS.Open Source:=strSQL, _
    ActiveConnection:=adocon, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockOptimistic

    adoRS.Update

    ActiveCell.Offset(1, 0).Activate

    a:
    ActiveCell.Font.Color = vbRed
    ActiveCell.Offset(1, 0).Activate


    Loop
    adoRS.Close
    adocon.Close
    Set adoRS = Nothing
    Set adocon = Nothing
    MsgBox (adoRS.GetString)
    End Sub

    Ive done database manipulation in VB.net and I am not sure how to use the executenonquery() and executescalar() method in ADO VBA. I also fail to understand why I cant use something like the below code which I use in VB.net and if it is possible to do it in ADO VBA.

    adocon.connectionstring="Provider=Microsoft.ACE.OLEDB.12.0;DataSource=C:\RefundsDB.accdb"
    adocon.open()
    strSQL="SAMPLE SQL INSERT,DELETE or UPDATE statement"
    sqlcomm=new Oledb.Oledbcommand(strSQL, adocon)
    sqlcomm.executenonquery()

    Thanks,

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    The code I posted is for a bulk insert into a table in SQL Server, your code appears to only insert one record at a time.

    The 2 methods you mention don't exist in ADO in VBA.

    Both seem to be executing SQL but they each return different things, ExecuteNonQuery returns the no of affected records and ExecuteScalar the first column of the first row in the result.

    Not sure about OLEDB in VBA, I've seen some suggestions that it can be used but not seen any decent examples.

    PS Could you add code tags when posting code? That makes it a whole lot easier to read (and copy) it.

  7. #7
    Registered User
    Join Date
    05-14-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    Hi Norie,

    Didn't know what a code tag was... got to know now

    So the code I posted, what is wrong with the On Error GoTo statement? Why does it not work after one iteration?

    Yes it inserts one at a time to an access database. This is what I'd like to implement at the moment but I needed to know how to do the same when I migrate to SQL server as well.

    What my code in VBA intends to do is: Start at cell A2 and store the activecell value in a variable, Offset by 1 column and store the value in a variable. This goes on till all the values are stored in variables so that it can be used in the SQL statement later.

    Then the code executes the SQL and if a duplicate DOES NOT already exist in the database then INSERT or else highlight the activecell in Red.

    Then the loop continues till all rows are added to the database or highlighted in Red for the user to review.

    You are right that ExecuteNonQuery and ExecuteScalar don't exist in VBA. Its part of Visual Basic.Net and not available in VBA. The example code at the bottom was taken from VB.Net to help understand what I am trying to achieve in VBA. These are not two methods, rather these are for different operations. When I want to execute a query without having to save the value into a variable I use ExecuteNonQuery and if I need to store in a variable I use ExecuteScalar.

    I have a logic in mind which I can use as a workaround if no one is able to tell why the On Error statement is not working, but I dont know how to store the result of the query into a variable.

    I hope this helps understand the situation, if not please do let me know.

    Thanks,

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA: Using SQL In Excel VBA To Extract Data To Access Or SQL Server

    Why not use a SELECT query via recordset to see if the MerchantID exists or not?

    You wouldn't need to put any values from the query in variables, just check if the recordset is empty.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1