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---

 

[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--

 

[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---

 

[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--

 

[ryguy7272]

That should help! Thanks again Cliff!!