Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Joining Excel Database with MSDE Database Table, & Import

    Looking for the basic procedure to join an Excel spreadsheet with a table from a database running on MSDE, and then updating the MSDE table with select columns of info from the Excel spreadsheet. I believe the first set of syntax will join the file with MSDE table, and the second set of syntax will run the import/update. If you can assist, please advise. Thank you in advance.

  2. #2
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi Benson,

    Below You find an example for exporting data to an MySQL-database.
    The only thing that differ from MS SQL 2000 is the connectionstring.

    Code:
    Option Explicit
    Sub Export_Data_ADO__MySQL()
       Dim cnt As ADODB.Connection
       Dim rst As ADODB.Recordset
       Dim wbBook As Workbook
       Dim wsSheet As Worksheet
       Dim rnName As Range
       Dim vaFName As Variant, vaEName As Variant
       Dim i As Long
    
       Set wbBook = ThisWorkbook
       Set wsSheet = wbBook.Worksheets("Blad2")
    
       With wsSheet
          Set rnName = .Range("A2:" & .Range("A65536").End(xlUp).Address)
       End With
    
       vaFName = rnName.Offset(0, 1).Value
       vaEName = rnName.Value
    
       Set cnt = New ADODB.Connection
       Set rst = New ADODB.Recordset
    
       cnt.ConnectionString = "DRIVER={MySQL};" _
             & "SERVER=localhost;" _
             & "DATABASE=XLDennis;" _
             & "UID=;PWD=;OPTION=3;"
    
      cnt.Open
      rst.Open "SELECT * FROM tblnamn", cnt, adOpenDynamic, _
             adLockOptimistic
    
      For i = LBound(vaEName) To UBound(vaEName)
          With rst
             .AddNew
             .Fields("FNamn") = vaFName(i, 1)
             .Fields("ENamn") = vaEName(i, 1)
             .Update
          End With
       Next i
    
       rst.Close
       Set rst = Nothing
       cnt.Close
       Set cnt = Nothing
    End Sub
    Following is a example of the connectionstring (it should be in one line only):

    Code:
    stCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=DENWAL"
    BTW, You need to set a reference to teh MS ADO Object Library in the VB-editor.

    I´m not exactly sure what You mean by joining but above will get You started.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    Dennis,

    Here is a brief description of my actual data:

    Excel File name = inventory.xls
    File location = C:\Documents and Settings\POS\Desktop\inventory.xls
    Excel current worksheet sample (both columns are 16 character strings):

    ITEMNO LOG_SOURCE
    A1 13110 B1 White 3 Ring
    A2 13111 B2 White 3 Ring
    A3 13112 B3 09/09/03
    A4 13113 B4 09/09/03
    A5 13114 B5 09/09/03

    SQL Table Name = ITEMS
    SQL current table sample (both columns are 16 character strings):

    ITEMNO LOG_SOURCE
    13110
    13111
    13112
    13113
    13114

    The primary objective is to update the LOG_SOURCE column of the SQL database with the values from the Excel worksheet (using ITEMNO as the link). Is all of the code you provided required to accomplish this type of standard update? Would you advise using DTS for this type of update, and if so, how do I access DTS (i.e. is it part of MSDE)? Thank you in advance for your time and expertise.

Posting Permissions

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