Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Transfer data from Excel to Access

    I have this code below, which uses DAO to automatically transfer data from Excel to Access.

    Can someone explain what the difference is from DAO and ADO? If I was to do the same project using ADO how could I do this? and the other issue is that the users that will be using the Excel report will not have MS Access installed on there PC. Does this change the way the code has to be written?

    Sub AddToMDB()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim r As Long

    Set db = DAO.DBEngine.OpenDatabase("D:\Work\MILtd.mdb")
    Set rs = db.OpenRecordset("tblExcelImport", dbOpenDynaset)
    r = 2
    Do While Len(Range("A" & r).Formula) > 0

    rs.AddNew
    rs![Day of Week] = Range("A" & r).Value
    rs!WeekNum = Range("B" & r).Value
    rs!Week = Range("C" & r).Value
    rs!ConcatDate = Range("D" & r).Value
    rs![HEAT - BCC] = Range("E" & r).Value
    rs![HEAT - Leeds] = Range("F" & r).Value
    rs![JD Heating] = Range("G" & r).Value
    rs![RG Francis] = Range("H" & r).Value
    rs!TotalDay = Range("I" & r).Value
    rs![Weekend?] = Range("J" & r).Value
    rs!User = "Jez L"
    rs.Update

    r = r + 1
    Loop
    Set db = Nothing
    Set rs = Nothing

    End Sub


    Thanks,
    Jez

  2. #2
    Join Date
    Feb 2007
    Posts
    3
    DAO & ADO object models are completely different and you can not mix and match. How much effort to convert will depend on complexity and extend you use the DAO object model. ADO is the new object model and is based upon the ODBC protocol. Often developers will use ADO when they desire to use Access as the GUI, but other databases for storage. (e.g. MS SQL)

    Not any value buying access licenses for everyone who will use the application. Definitely need to buy one for each developer and the DBA. Off the top of my head to options come to mind. Develop front end with a different tool or use Access Runtime to distribute the application.

    I think the following article will be of value to you. http://www.developer.com/tech/article.php/721181

    Good Luck

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks for that, unfortunately I can only have the Microsoft Office Professional. What I am thinking of doing is creating my project in Excel (for the end users) and then sending the data to a database so that I can create some reports from the raw data held in the database.

    Do you think thats a good way of doing it?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what i have done is

    link the excel file into msaccess and them in access create a make table
    and run that job done
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    I have created a couple of projects using DAO and ADO to send and receive data to Access and other databases from Excel.

    Is it a good way of doing it? It can get complicated. I think it works better when you have very specific and limited data and the users understand what they are doing.

    With Excel it is easy for users to mess up the worksheet structure and enter invalid data unless you work to lock down the process, validate input and use error checking.

    This code Example is from a project where data was pulled from the MDB file to display in a Excel read-only for the user. After updating dates and status of the records the user could update the data to the MDB. This sub sends data to the MDB.
    Code:
    Sub ADOsendExtractData()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim conn As Object, rs As Object, r As Long
    Dim dbFilePath As String
    Dim strRAnumber As String
    Dim strTS As String, strSql As String
    Dim strMsg As String
    
    dbFilePath = ThisWorkbook.Path & "\RAData.mdb"
        
        strRAnumber = GetDocProperty("Document Number")
        If Not Len(strRAnumber) > 0 Then
          strRAnumber = ActiveSheet.Name
        End If
        
        If Not strRAnumber Like "RA#######" Then
            MsgBox "RA: '" & strRAnumber & "' is not valid" & vbCrLf & vbCrLf & _
            "The active workbook must be a valid Excel Extract workbook with a RA Number specified", vbOKOnly + vbInformation, "RA Add Records"
            Exit Sub
        End If
        
        
        ' connect to the Access database
        Set conn = CreateObject("ADODB.CONNECTION")
        conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=" & dbFilePath & ";"
        
        r = 2 ' the start row in the worksheet
        Do While Len(Cells(r, 1).Formula) > 0
        ' repeat until first empty cell in column A
          strTS = Now()
          strSql = "INSERT INTO [Aircraftstatus] ( [RA Number], Model, SN, Analyst, TS ) " & _
                  " VALUES (" & _
                  "'" & strRAnumber & "'," & _
                  "'" & Cells(r, 1) & "'," & _
                  "'" & Cells(r, 2) & "'," & _
                  "'" & Cells(r, 3) & "'," & _
                  "'" & strTS & "')"
                  
    ' INSERT INTO Aircraftstatus ( [RA Number], Model, SN )
      On Error Resume Next
     conn.Execute strSql
    
      If Err <> 0 Then
        Select Case Err.Number
            Case Is = -2147467259
              strMsg = "Record already exists"
            Case Else
              strMsg = "Error " & Err.Number & " " & Err.Description & "!"
        End Select
        
        Cells(r, 5) = "Failed: " & strMsg
      Else
        Cells(r, 5) = "Loaded"
        Cells(r, 6) = strTS
      End If
      
      On Error GoTo 0
    
            r = r + 1 ' next row
        Loop
        Set rs = Nothing
        conn.Close
        Set conn = Nothing
    End Sub
    
    ''''''''''''' Functions ''''''''''''''
    
    Public Function GetDocProperty(strPropName As String)
    On Error GoTo GetDocProperty_Fail
    GetDocProperty = ActiveWorkbook.CustomDocumentProperties(strPropName)
    
    GetDocProperty_Fail:
      GetDocProperty = ""
    End Function
    The validation and integration of data to the display form is much easier using Access forms. If you can create the app in access and distribute as a run time that is easier.
    ~

    Bill

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Bill,

    Thanks for the reply. I understand what you mean about the validation being crucial on this as users can sometimes enter any old rubbish in cells.

    Someone else pointed out creating the application in Access and then distributing as a Run Time Project. I only have MS Office Professional, do I need specific software to do this Run Time application? If so what do I need or if it can be done in Office Pro, where can I find it in this?

    Jez

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    By writing MS Office Professional, do you mean that you have MS Access?

    If you use this code, I suspect that in VBE you will need to reference MS Access Library (In VBE, go to Tools > References, then on left scroll down to find MS Access and check the box next to it).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I dont understand what you mean...

    I have MS Access in MS Office Pro yes. I was asking about the Run Time Project, do I need specific software to do this Run Time application? If so what do I need or if it can be done through Access in Office Pro, where can I find it in this?

    How do you mean suspect that in VBE you will need to reference MS Access Library (In VBE, go to Tools > References, then on left scroll down to find MS Access and check the box next to it)?

    Jez

  9. #9
    Join Date
    Feb 2004
    Posts
    533
    The runtimes I created I just made the MDE file. IT would install the runtime on the users computer who needed to run the program from the network by request.

    To make the runtimes you need a copy of Access Developer Extensions. The Access 2007 Runtime is a free download. 2003 Access Runtime you must purchase Visual Studio for MS Office (about $500).

    Deploying Access 2007 Runtime-Based Solutions

    Access 2003 Developer Extensions are part of the new Visual Studio Tools for the Microsoft Office System.
    Access 2003 Developer Extensions
    ~

    Bill

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Bill,

    Does this mean that if I get this software that when I create a MDE file it will work on anyones PC regardless of if they have MS Access or not. This is because the users dont have Access and the Company will not pay for it. Is this a solution other than me creating the application in Excel and using a database to hold the data?

    Jez

  11. #11
    Join Date
    Feb 2004
    Posts
    533
    Making the MDE only compresses the DB and compiles the code. This gives you some protection to prevent users from poking around your program and messing with stuff they don't need to see. Like the VB Modules and Form Edit View. It does not allow the program to operate on PCs that do not have MS Access installed.

    MS Access Online Help: saving your Microsoft Access database as an MDE file compiles all modules, removes all editable source code, and compacts the destination database. Your Visual Basic code will continue to run, but it cannot be viewed or edited.

    In order for users to run your MS Access program without the full MS Access Application they would need the MS Access Runtime files installed. For that you will need MS Visual Studio for MS Office or MS Access 2007 Runtime download from the Microsoft site. (links in prior post).

    The typical way to set this up on a network would be to have your Database in one MDB file and your program for distribution in another MDB file with links to the tables in the Database MDB. (A backend Database and frontend User Interface) Once the program is configured you will save it as an MDE and place it in the network location where the users may access it. Then each user will need the Access Runtime installed to run the program without having the MS Access application installed.

    The advantage of creating the UI program in Access vs Excel is for ease of development. Access has built in tools for configuring the queries, forms, etc. to work directly with the Access MDB.
    ~

    Bill

  12. #12
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Going back to my original thread, as speaking with my employers that arent prepared to spend the money on the Run Time developers application at the present time. Hopeless cant win with them!!!

    So anyway my original thread, this code below works really well and quick, but my question is that when I have a blank field in one of the rows I am importing it will fall over, how can I get around that if its null then still import as it can be a null field apart from the first field in the row( in this case A)?

    Sub AddToMDB()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim r As Long

    Set db = DAO.DBEngine.OpenDatabase("D:\Work\MILtd.mdb")
    Set rs = db.OpenRecordset("tblExcelImport", dbOpenDynaset)
    r = 2
    Do While Len(Range("A" & r).Formula) > 0

    rs.AddNew
    rs![Day of Week] = Range("A" & r).Value
    rs!WeekNum = Range("B" & r).Value
    rs!Week = Range("C" & r).Value
    rs!ConcatDate = Range("D" & r).Value
    rs![HEAT - BCC] = Range("E" & r).Value
    rs![HEAT - Leeds] = Range("F" & r).Value
    rs![JD Heating] = Range("G" & r).Value
    rs![RG Francis] = Range("H" & r).Value
    rs!TotalDay = Range("I" & r).Value
    rs![Weekend?] = Range("J" & r).Value
    rs!User = "Jez L"
    rs.Update

    r = r + 1
    Loop
    Set db = Nothing
    Set rs = Nothing

    End Sub

  13. #13
    Join Date
    Feb 2004
    Posts
    533
    Thats funny, Management is afraid of spending money, so you can take 10 times the labor to develop a custom program. I have to deal with this mentality all the time.

    Your problem with the Update Error on Null values is because you must encase your values in single quotes. The way I like to do this is to define a query in a string variable, then run the query. This will give you much more power in the ability to run Updates, Insert, or select queries pretty much using a couple functions to send commands to your DB. You only need to change the parameters. You can even work up the queries in MS Access, then copy the query and use it in your script with a little doctoring to insert the variables.

    This isn't fully complete code but just to give you an idea how to code this. You need to set a reference for "Microsoft ActiveX Data Objects 2.5 Library" to use ADO methods in your script. (you were using DAO in the Example in your previous post wich is OK if it works) I used ADO in my project.
    Code:
     
    dbFilePath = ThisWorkbook.Path & "\RAData.mdb"
    strRAnum = Cells(r, 1)
    
    strSQL = "UPDATE RAList SET " & strDBCols & ",RAList.TS ='" & Now() & "'" & _
            "  WHERE (((RAList.[RA Number])='" & strRAnum & "'))"
            
     ' Debug.Print strSql
    
      Set oADO = New ClassDataLayer
      Set oConnect = oADO.Connect(dbFilePath)
      
      oADO.Query strSQL
    
      oConnect.Close
      Set oConnect = Nothing
      Set oADO = Nothing
    These functions I have in a class module and then send SQL strings to the Functions to open the database connection and send query commands.
    Code:
    Public oConn As Object
    
    Function Connect(dbFile As String) As Object
    
      'Open an ADO connection to the folder specified
      Set oConn = CreateObject("ADODB.CONNECTION")
      oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & dbFile & ";"
      '& _
      '"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
      
      Set Connect = oConn
    End Function
    
    
    Function Query(strQuery As String)
     Dim oRS As Object
     Set oRS = CreateObject("ADODB.RECORDSET")
     'Now actually open the text file and import into Excel
      oRS.Open strQuery, oConn, 3, 1, 1
      Set Query = oRS
    End Function
    ~

    Bill

  14. #14
    Join Date
    Sep 2011
    Posts
    2
    To answer your initial question, wouldn't it work if you changed the do while > 0 to do while .eof?

    regards,

    Pascal78

Posting Permissions

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