[RESOLVED] Accessing Access DB Without Access

[MartinLiss]

It's been ages since I've done anything with Access but I seem to remember that I should be able to read/write to an Access database as long as that's all I want to do and I don't need reports or forms, etc. How do I do that from Excel and have the Access database be sharable by multiple users?

[westconn1]

reference to ADO

basic connection to existing

Dim cn As ADODB.Connection, rs As Recordset
Set cn = New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & "\qbinfo.mdb"
Set rs = New Recordset
rs.Open "select * from qbinfo", cn, adOpenDynamic, adLockOptimistic
do until rs.eof
  cnt = cnt +1
  rs.movenext
loop
msgbox cnt & " records returned"

change source path and table names to suit

you can use sql to use criteria for returned records
ado has find and filter methods to locate or filter the records based on some criteria
if your database has password etc, see connectionstrings.com for ...............

s long as that's all I want to do and I don't need reports or forms, etc

you can still have all those, but you have to design your own

i often use access databases, but i have never had access installed

[Spoo]

Marty

Short answer, seems to me, would be to write a macro using Excel VBA that ,,
1. Reads the Access file
2. Writes to the Access file
3. Does other stuff on a spreadsheet in the Excel file.

I have not specifically tried this, but, despite the fact that VBA is a crippled
version of VB6, I imagine that it is doable using VBA.

How do you plan to (ahem) access Access?
DAO
ADO

If DAO, and you need some further help, holler.

And, oh yeah, what happened to your other thread?

EDIT

Dang ,, Westconn1 snuck in a post while I was composing.

Spoo

[MartinLiss]

Marty

Short answer, seems to me, would be to write a macro using Excel VBA that ,,
1. Reads the Access file
2. Writes to the Access file
3. Does other stuff on a spreadsheet in the Excel file.

I have not specifically tried this, but, despite the fact that VBA is a crippled
version of VB6, I imagine that it is doable using VBA.

How do you plan to (ahem) access Access?
DAO
ADO

If DAO, and you need some further help, holler.

And, oh yeah, what happened to your other thread?

Spoo

1 to 3: Yes that's exactly want I want to do and other than the apparently non-trivial task of actually opening the database (read on) I'm sure I can do it with ADO. As for my other similar thread I thought it got off to a bad start so I decided to try again.

@westconn1: The database I created via Access 2010 wound up with an extension of accdb and I get an "AutomationError Unspecified Error" on this line.
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\MyPath\test.accdb"

[Spoo]

Marty

10-4.

I'm still an Access 2003, DAO kinda guy, so I can't help much regarding
the connection. Once you work that bit out, I might be able to address
other issues you may have, assuming Westconn1 doesn't beat me to the
punch again ,,

Spoo

[si_the_geek]

accdb files can't be used by Jet, so there are two main choices.

The first and easiest is to open the database file in Access and do a "save as" (or whatever it is called in that version) to convert it to an mdb file. Access 2007 can work with both, so I assume Access 2010 can too.

The other option is to download and install the 'replacement' provider ACE, and change the connection string to suit. You will however need to install it for the other users too, and I seem to remember there are conflicts between the 32-bit and 64-bit versions of ACE which mean that they can't be installed at the same time (which could cause problems for some people).

[MartinLiss]

Strangely accdb seems to be the only choice.

[Siddharth Rout]

Marty, will your users be using 32 bit MS Office (Not Windows) or 64 bit?

[westconn1]

i use visdata to create most databases (mdb), it is installed as part of vb6, though it is quite possible to create database from scratch using code

if you do not need to use the database within access at all there are other choices, including sql and mysql, both i believe are available to use at no cost

[Spoo]

WestConn

You sly dog

i often use access.databases, but i have never had access installed

That sounded, well, somewhat iffy.

i use visdata to create most databases (mdb), it is installed as part of vb6,

Aha !! Mystery solved. No longer iffy.
Who knew?

Spoo

[MartinLiss]

@sid: All will be Windows PCs but I don't know if the operating systems are consistent. I can find out though. BTW I love the logo in your signature.
@westconn1: I'll look into the Visual Data Manager.

[MartinLiss]

Thanks everyone.

[westconn1]

for anyone reading this thread

there are update instructions, on the internet, for the visdata project, to enable visdata to work with /create access 2000 databases, it is simple to update the project then recompile to create an updated exe
the original version only works with access 97 databases

even when updated, visdata is fairly dated, but it suits my needs well, if i need a better database than access 2000 i would go to mysql or similar