there is no 64-bit Jet (MS Access) OLEDB driver.

[Darin]

Since there is no 64-bit Jet (MS Access) OLEDB driver, what is the
recommended solution for this delimma.

Our application uses SQL Server as the daatabase engine, but we have
about 5 meg of static data stored in an Access database. We chose that
because it is easy to read, and can be password protected.

Since Microsoft doesn't have a 64-bit OLD to Access, the application has
to be set to run in 100% 32-bit mode to get it to run. This defeats the
purpose of having a 64-bit OS.

What are my options here?
Thanks.

Darin

 

[Michael D. Ober]

Since you are already using SQL Server, why not move your static data to it
and use Access to link to the SQL static data?

Mike.

 

[Darin]

Ok, except that if it is in the SQL database, then when we install an
update on their system something needs to write new static data to the
SQL db, that was the purpose of Access - we can supply the MDB and it
never changes.

Our MDB has the menu (old access switchboard-type logic), and all of the
SQL definations for view, stored procedures, functions, etc.

Darin

 

[Michael D. Ober]

Split your access into a front end user interface and a back end database.
Then move the back end to SQL Server.

Mike.

 

[Darin]

THe reasons for selecting the access mdb at the beginning was:

1. secure - can be password protected.
2. requires nothing additional on the computer to read it - everything
is builtin
3. Can supply a new mdb w/ ease for updates.

I am looking for something that is 64bit and has the same features.

THanks.

Darin

 

[Michael D. Ober]

THe reasons for selecting the access mdb at the beginning was:

1. secure - can be password protected.

Access's password is realatively easy to break.

2. requires nothing additional on the computer to read it - everything
is builtin
3. Can supply a new mdb w/ ease for updates.

Take a look at SQL Compact. I think this will do what you are looking for.

I am looking for something that is 64bit and has the same features.

THanks.

Darin

Mike.

 

[Cor Ligthert [MVP]]

Darin,

If it is that static, than why not just an XML dataset file?

Cor

 

[Michael D. Ober]

Cor,

I think OP's real issue is that he already has a working management front
end in Access and doesn't really want to change this.

Mike.

 

[Cor Ligthert [MVP]]

Michael,

I agree, however the change from an access database to an inside file is as
well an alternative as the SQL server alternative you gave. If he uses than
an SQL server as he already does, then he has an easier way with XML files
to create those.

(I hope it is readable I have changed the text 25 times)

Cor

 

[Darin]

My front-end is VB.NET, not access. The access MDB only contains all the
SQL text for views, stored procedures, etc.

I can look at an XML file, I haven't worked with any yet. I know from
reading the XML don't load real fast, but this file wouldn't be read all
the time, only when an update is done.


Darin

 

[Cor Ligthert [MVP]]

From your SQL computer you can make it by

writeXML(mypath)

Cor

 

[jeff]

Darin,

your 'static' dataset is only read once - when an update is done. So, once
the user updates the application, you mdb is not going to accessed or used
again until the next update? Or is it read each time the user STARTS the
application ? I am a bit confussed here?

If the first ... read only once after the update ... why not use the xml
file approach. Speed will be an issue, but if it is a part of the update
process, the user can probably live with it - especially if there is a
noticable performance gain from utilizing the 64 bytes OS. However, if you
are pushing out updates every 4 hours, the user may become a little annoyed.

I would suggest that you test the 'speed' of loading an xml file specfic to
you needs. Many people over-use xml files and try to use them as a
'database' and expect the same results / speed they get from a database. Is
you Static database hugely relational or is it a few 'independant' tables
with a lot of rows in each? This will impact the speed of reading the xml
file.

If the second situation - read everytime the application is fired up, you
will have to either live with the deficiency with the Access OLEDB driver,
or you will need to test the loading of the XML and come up with a design
approach that offsets the performance / restrictions of running your
application in 100% 32-bit mode.

Jeff.

PS: I would suggest developing a process that utilizes sql db on the client
and issue a SQL file to be loaded with each update.

 

[Michael D. Ober]

Please tell me you're not using the "default" database in SQL. You can have
multiple databases in SQL Server - put your static in a named database. It
will have its own files that can be replaced as needed.

Mike.

 

[Darin]

Right now, I am changing it so the MDB is only accessed once, at an
update when needed to be done.

I need to figure out how to create the XML for it.

Darin

 

[Paul Clement]

¤ Since there is no 64-bit Jet (MS Access) OLEDB driver, what is the
¤ recommended solution for this delimma.
¤
¤ Our application uses SQL Server as the daatabase engine, but we have
¤ about 5 meg of static data stored in an Access database. We chose that
¤ because it is easy to read, and can be password protected.
¤
¤ Since Microsoft doesn't have a 64-bit OLD to Access, the application has
¤ to be set to run in 100% 32-bit mode to get it to run. This defeats the
¤ purpose of having a 64-bit OS.
¤
¤ What are my options here?

Quite limited I'm afraid. Unless Microsoft changes their mind and develops a 64-bit provider for
Access you will have to go with a 64-bit database alternative.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Darin]

I will do an XML, but I have NO experience w/ them.

My table is application, type, name, sql text, with the key being
application, type, and name.

How would I setup the XML to have those keys so I can find what I need,
or is that possible?

Darin

 

[Guest]

Darin,

If you are using SQL Server 2005 can you deliver your static data as an MDF
file, in the same way that you currently deliver that data in an Access MDB
file?

Kerry Moorman

 

[Darin]

Yes, but I would need to first delete the database, then attach the MDF
- right?

Darin

 

[aaron.kempf]

for the record-- every SQL Server database that _I_ work on?

I call it SQLDB_20070425

and then the next day U call it SQLDB_20070426

then.. when you need to deploy a new version it's easy to keep track
of.

 

[Vayse]

If you're using Access 2003, you can use File/Export to create XML files
from your tables.
Vayse