Unrecognized database format ‘C:\Trading System’

[ryguy7272]

Unrecognized database format ‘C:\Trading System’

I’m working in 2007 (finally took the leaf of faith). Why would I get the
error described above? My code is below:

Private Sub Post_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\Trading System.accdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

' Open contact table.
Set rstcontact = New ADODB.Recordset
rstcontact.CursorType = adOpenKeyset
rstcontact.LockType = adLockOptimistic
rstcontact.Open "Options", cnn1, , , adCmdTable

'get the new record data
rstcontact.AddNew
rstcontact!OptionNo = OptionNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
'rstcontact!Month = Month

rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved

rstcontact!DateAdd = Now()

rstcontact.Update

' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"

'close connections
rstcontact.Close
cnn1.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

I think the problem is coming from here:
mydb = "C:\Trading System.accdb"

I am referencing Microsoft ActiveX Data Objects 2.8 Library
I tried to reference Microsoft DAO 3.6 Object Library because this is what I
had when I used this code in Access 2002. This must be a simple fix, right.
One of the references is not correct?

Also, I now have this saved on my C-drive. How can I make sure the Form
will write to the Table no matter where the database is saved? I’m giving
this file to a friend when I am done with it. I think he is going to put it
on his corp. network (intranet). Is there some way to set this up so the
database doesn’t have to be saved to a specific place? I know a few people
in his office will use it. I know he will not save the file on hid cC-drive,
like I did. Any thoughts on this?

Thanks so much!
Ryan---

 

[Clifford Bass via AccessMonster.com]

Hi Ryan,

You need to use the newer provider as the Jet 4 provider does not
recognize Access 2007 files. So use "Microsoft.ACE.OLEDB.12.0" instead of
"Microsoft.Jet.OLEDB.4.0".

Clifford Bass

 

[ryguy7272]

Thanks for the tip Cliff, but I think it may be something else too.

With your change, I now get this:
run-time error '2147467259 (80004005)
Could not find file 'C:\Trading system.accdb'

This line is yellow:
cnn1.Open strCnn

I am referencing Microsoft ActiveX Data Objects 2.8 Library. I tried to
reference Microsoft DAO 3.6 Object Library because this is what I had when I
used this code in Access 2002, but when I click on the reference, I get a
message saying 'name conflicts with esisting module, project, or object
library'

If I remove the Microsoft ActiveX Data Objects 2.8 Library and to add the
Microsoft DAO 3.6 Object Library, I get the same message. Is there something
new for this in Access 2007?

Thanks!
Ryan--

 

[Clifford Bass via AccessMonster.com]

Hi Ryan,

Oops, I forgot something. In your references remove the ADO 2.8 Library
and add "Microsoft Office 12.0 Access Database Engine Object Library". That
should do it.

Clifford Bass

Thanks for the tip Cliff, but I think it may be something else too.

With your change, I now get this:
run-time error '2147467259 (80004005)
Could not find file 'C:\Trading system.accdb'

This line is yellow:
cnn1.Open strCnn

I am referencing Microsoft ActiveX Data Objects 2.8 Library. I tried to
reference Microsoft DAO 3.6 Object Library because this is what I had when I
used this code in Access 2002, but when I click on the reference, I get a
message saying 'name conflicts with esisting module, project, or object
library'

If I remove the Microsoft ActiveX Data Objects 2.8 Library and to add the
Microsoft DAO 3.6 Object Library, I get the same message. Is there something
new for this in Access 2007?

Thanks!
Ryan--

Hi Ryan,

[quoted text clipped - 80 lines]

 

[Clifford Bass via AccessMonster.com]

Hi Ryan,

You may need to keep the ADO library. I may be wrong on telling you to
delete it. I am not where I can test it properly for you until Tuesday. The
provider I gave you should do the trick. Make sure you have it typed in
correctly. A good source for connection strings is <
http://www.accessmonster.com/>.

Clifford Bass

Hi Ryan,

Oops, I forgot something. In your references remove the ADO 2.8 Library
and add "Microsoft Office 12.0 Access Database Engine Object Library". That
should do it.

Clifford Bass

Thanks for the tip Cliff, but I think it may be something else too.

[quoted text clipped - 23 lines]

 

[ryguy7272]

Thanks again Cliff! I tried what you recommended and am still getting an
error, albeit a new error:
Compile Error: User-defined type not defined
This line is highlighted:
Dim cnn1 As ADODB.Connection
I'm going to do some more research on this.

Also, please notice the last part of my original question.
I now have this saved on my C-drive. How can I make sure the Form will write
to the Table no matter where the database is saved? I’m giving this file to a
friend when I am done with it. I think he is going to put it on his corp.
network (Intranet). Is there some way to set this up so the database doesn’t
have to be saved to a specific place? I know a few people in his office will
use it. I know he will not save the file on his C-drive, like I did. Any
thoughts on this?

Thanks,
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hi Ryan,

You may need to keep the ADO library. I may be wrong on telling you to
delete it. I am not where I can test it properly for you until Tuesday. The
provider I gave you should do the trick. Make sure you have it typed in
correctly. A good source for connection strings is <
http://www.accessmonster.com/>.

Clifford Bass

Hi Ryan,

Oops, I forgot something. In your references remove the ADO 2.8 Library
and add "Microsoft Office 12.0 Access Database Engine Object Library". That
should do it.

Clifford Bass

Thanks for the tip Cliff, but I think it may be something else too.

[quoted text clipped - 23 lines]

Thanks so much!
Ryan---

 

[Clifford Bass via AccessMonster.com]

Hi Ryan,

Okay, I have had a chance to do a little testing. Here is what works
for me. It requires the "Microsoft ActiveX Data Objects 2.x Library" (I used
2.8) and the "Microsoft Office 12.0 Object Library". It will prompt for an
Access 2007 database that the user can select. I did not test the writing to
the recordset. Note that I made a few changes to your code. It is important
not to create a variable named err as there is a global object named Err that
is used with error processing. It is better to use the built-in error
processing features. See below for example and see online help for details.

Private Sub Post_Click()

' Getting rid of "Dim err As Integer" as it could
' cause problems with the actual Err object
' Use error processing instead
On Error GoTo Handle_Error

Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
' Getting rid of some items that are really not
' needed
' Adding a FileDialog object (requires Microsoft
' Office 12.0 Object Library reference)
Dim fd As FileDialog

Set fd = FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Access 2007 Databases", "*.accdb"
.Filters.Add "All Files", "*.*"
.InitialView = msoFileDialogViewList
.Title = "Select Database"
If .Show Then
' Open a connection.
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & .SelectedItems.Item(1)

' Open contact table.
Set rstcontact = New ADODB.Recordset
rstcontact.CursorType = adOpenKeyset
rstcontact.LockType = adLockOptimistic
rstcontact.Open "Options", cnn1, , , adCmdTable

'get the new record data
rstcontact.AddNew
rstcontact!OptionNo = OptionNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
'rstcontact!Month = Month

rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved

rstcontact!DateAdd = Now()

rstcontact.Update

' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " & _
rstcontact!TicketNo & " has been successfully added"
End If
End With

Exit_Sub:
On Error Resume Next

' Close connections if open
If Not cnn1 Is Nothing Then
If cnn1.State <> ADODB.ObjectStateEnum.adStateClosed Then
cnn1.Close
If Not rstcontact Is Nothing Then
If rstcontact.State <> ADODB.ObjectStateEnum.adStateClosed
Then
rstcontact.Close
End If
End If
End If
End If

Exit Sub

Handle_Error:
MsgBox "Error #" & Err.Number & " has occurred, please check and try
again" & _
vbCrLf & vbCrLf & "Description: " & Err.Description
Resume Exit_Sub

End Sub

If you still get an error, there could be something amiss with your
Office 2007 installation. In which case you could try doing a repair or a
reinstall.

Hope that helps,

Clifford Bass

 

[ryguy7272]

Thanks once again Cliff! I made the recommended changes and ran the code. I
promptly encountered a Windows ‘look in’ Window. I navigated to my DB,
clicked on the file, and was greeted with this message ‘ms access the
database has been placed in a state by user admin on prevents it from being
opened or locked’. It kind of makes sense, because it seems like Access
wanted me to open Access, from itself, when it was already open. Not sure
what to make to make of this new 2007 and I’m not sure I can help this friend
because we are on opposite sides of the US and I can’t even get Access 2007
to open now (for some reason it has to open itself). I don’t think I can
talk him through setting it up on his network, when I am 3,500 miles away.

I’ll have to research this more because I still want to understand the
concept of posting an Access Db on a corporate network.

Thanks for the effort Cliff, and Happy Holidays to you.

Ryan--

 

[Clifford Bass via AccessMonster.com]

Hi Ryan,

Basic question: Are you trying to choose the database that you are
already in? Or, are you trying to choose a database that you have opened
exclusively in another instance of Access If so, it will give you that error.
The presumption is that you are choosing a different database and that that
database has not been opened by someone (yourself or someone else) in an
exclusive mode. Note that making changes to forms or code or other things
usually places a database in exclusive mode. Note that this is not trying to
open "Access" itself; but a different database file. If Access has gotten
confused and will not open, which can happen, just do a reboot.

For putting a database on a network, you will want to do a split
database, with the back end (data only) on the network and the front end
(forms, code, queries, etc.) on each user's computer. See Allen Browne's web

. See Tony Toews web site <http://autofeupdater.com/> for a useful tool.

Hopefully some of that will be of help to you. If not, post back with
specifics.

Clifford Bass

 

[ryguy7272]

That should help! Thanks again Cliff!!

 

[Clifford Bass via AccessMonster.com]

Hi Ryan,

Good to hear. You are welcome!

Clifford Bass