Solved

Excel To Access Database Transfer

Posted on 2016-10-17
14
85 Views
Last Modified: 2016-10-18
Hi guys

I am using the below code to transfer data from excel to access but as soon as I change the database version from mdb to accdb it gives an error unrecognizable format, so this code is working for 2002-2003 format but not for 2010 version.

Sub Data_Transfer2()
Dim SaveTime As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset


'Dim clipboard       As MSForms.DataObject
Dim i, j As Integer
Dim Lastrow As Integer

On Error GoTo Err:
      
'//Database Location
'Changed so that people with different drive mappings wont have issues

Const DB_LOCATION = "\\au2004np0012\Tcsgroup\SCHEDULE ADMINISTRATORS-\Quality Assurance\Change.accdb"


    '//Table has a datecreated/datemodified timestamp for each record
    SaveTime = Now
  
  i = 2
    '//Open Database
    If db Is Nothing Then
       Set db = DAO.Workspaces(0).OpenDatabase(DB_LOCATION)
    End If
 For j = 1 To Worksheets("Sheet1").Range("P1")
    '//Open Table
    If rs Is Nothing Then
        Set rs = db.OpenRecordset("Change", dbOpenDynaset)
    End If
      
    
    '//Create a new record
    With rs
    
        .AddNew
        ![FName] = Sheet1.Cells(i, 1).Value
        '![Brand_Name] = Sheet1.Cells(i, 1).Value
        '//isCompany has been removed. Defaults to false, database does not use.
        ![LName] = Sheet1.Cells(i, 2).Value
        ![Initial] = Sheet1.Cells(i, 3).Value
        '![SalaryID] = Sheet1.Cells(i, 4).Value
        ![SaralyID] = Sheet1.Cells(i, 4).Value
       ' ![custPosition] = Sheet1.Cells(2, 1).Value
        ![Agent_ID] = Sheet1.Cells(i, 5).Value
         ![SiteLocation] = Sheet1.Cells(i, 6).Value
          ![Date_Effective] = Sheet1.Cells(i, 7).Value
           ![Email] = Sheet1.Cells(i, 8).Value
            ![Team] = Sheet1.Cells(i, 9).Value
             ![Change_Action] = Sheet1.Cells(i, 10).Value
                ![Comment] = Sheet1.Cells(i, 11).Value
                 ![Status] = Sheet1.Cells(i, 12).Value
                    ![Add By] = Application.UserName
       
      '  ![requestTime] = Now
        '//Insert Record into Database
        .Update
        insertRecord = True '//SUCCESSFUL INSERTION
        
       i = i + 1
     
    End With
 Next j

 Sheet1.Range("a2:k10000").Select
Selection.ClearContents
 SendKeys "{ESC}"
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    Exit Sub

Err:
    Select Case Err.Number
        Case 3024
            MsgBox "Error: Could not establish connection to DB: Could not find file"
            Exit Sub
        Case 3008
            MsgBox "Error: Could not establish connection to DB: Opened exclusively"
            Exit Sub
        Case -2147221040
            Resume Next
        Case Else
            MsgBox Err.Number & ": " & Err.Description
            Exit Sub
        
    End Select
End Sub

Open in new window

0
Comment
Question by:surah79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41847566
on which line of code does the error occur?
and are you able to take, and upload, a screenshot of the tools\references (access this via the vba editor screen/menu)
thanks
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41847577
what DAO Object Library ver was used in your program? you may want to remove it and use library such as Microsoft Office 14.0 Access Database Engine Object Library for accdb file instead.
0
 

Author Comment

by:surah79
ID: 41847583
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41847596
@surah79,

have you tried my suggestion? it works for me.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41847625
maybe experts should wait until the author responds to the first question before adding a bunch more?
0
 

Author Comment

by:surah79
ID: 41847692
still giving the error in the below line
Const DB_LOCATION = "\\au2004np0012\Tcsgroup\SCHEDULE ADMINISTRATORS-\Quality Assurance\Change.accdb"

Open in new window

0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41847744
try to have something like this ?

SnapShot1.png
0
 
LVL 84
ID: 41848367
<No points please>

You cannot open .accdb databases using DAO 3.6. It simply will not work. As RyanChong said, you MUST use the newer Access Database Engine Object library.
0
 

Author Comment

by:surah79
ID: 41849202
do I have to change anything in the code after changing the reference as it is closing my excel and not transferring any data after changing the reference? Hi ryan can you send me the code which you are running after changing the reference?


Thanks
0
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41849382
do I have to change anything in the code after changing the reference as it is closing my excel and not transferring any data after changing the reference?
not necessarily.

you can try attached.
28976988.xlsm
0
 

Author Comment

by:surah79
ID: 41849411
Hi Ryan

This is error which I am getting while running the macro from your file.


Thanks
Error2.png
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41849414
SnapShot1.pngworking well for me, can you change the DB_LOCATION to a valid path (or first using a local path) and re-test? or you can also retest using another machine, see what's the potential issues your Excel is crashing.
0
 

Author Comment

by:surah79
ID: 41849421
all done my access database got corrupted so I put a new one now its working very well.

Thanks a lot for your help.
0
 

Author Closing Comment

by:surah79
ID: 41849422
thanks a lot
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question