X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Excel Error When Linking To Access 2010 Database, Access 2010    
post Oct 3 2014, 01:26 PM

Posts: 19
Joined: 8-March 11

I converted an Access 2003 database to Access 2010. I have an Excel spreadsheet that is filled out by a user and then sent out via email. When the command button to send it clicked the info in the spreadsheet is filled into the Access database. In the Excel file there is a module has the path to the database. Now that the database is an *.accdb and not *.mdb format I get an error 3343 saying it is an unrecognized database format. when I click debug it goes to this line "Set Dbs = wrkJet.OpenDatabase (strDPath, False, True)". I'm assuming it has something to do with the type of connection (wrkJet??) but I don't understand enough about Access to know what to do. The Access database engine 2010 has been installed. I'm using Window 7, 32 bit system. Here is some coding which might help you understand where the error is coming from. Help!
Option Explicit
'This variable contains the path to the Access database into which issue report records are written.
Public Const strDPath As String = "C:\Network\Issues_be_2010.accdb"
Private Declare Function MakeSureDirectoryPathExists Lib _
"IMAGEHLP.DLL" (ByVal DirPath As String) As Long
Sub LoadDistricts(cboReg As ComboBox, cboDist As ComboBox)
'Once users choose the Regional office, narrow the choices they have for District office names.
If cboReg.ListIndex > 0 Then
cboDist.ListFillRange = "Region" & CStr(cboReg.ListIndex) & "Districts"
cboDist.ListIndex = -1
cboDist.ListIndex = -1
cboDist.ListFillRange = "Districts"
End If
End Sub
Function GetIssueID() As Long
'This routine returns the issue ID assigned to the issues report that the user has submitted.
'This ID is then used, in another subroutine, to add reference information to the subject line of an email.
Dim wrkJet As DAO.Workspace, Dbs As DAO.Database, Rs As DAO.Recordset
Dim i As Long

'Open connection to issue database
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set Dbs = wrkJet.OpenDatabase(strDPath, False, True)
Set Rs = Dbs.OpenRecordset("Issues", dbOpenTable, dbReadOnly)

'Retrieve the ID of the last record in the database
If Rs.RecordCount = 0 Then
i = 0
i = Rs.Fields(0).Value
End If
GetIssueID = i
End Function
Go to the top of the page
post Oct 3 2014, 02:43 PM

Posts: 786
Joined: 25-April 14

Usually, that comes from an older Excel that cant read a newer Access db.
I would have kept the old .mdb format, new access still reads it and you have backward compatablilty.
New excel sheet, in new excel app, should be able to read New Access.
Go to the top of the page
post Oct 6 2014, 07:33 AM

Posts: 19
Joined: 8-March 11

The root cause of the issue is because in the Access 2003 database I has a Calendar button that would no longer work in Access 2010. I converted the 2003 database to a 2010 database and fixed the buttons so the calendar would work. I also converted the spreadsheet to Excel 2010 xlms format. However the conversion to Access 2010 required me to change the link in Excel to point to an accdb file. I can only see the option of fixing the Excel code.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:31 PM