Accessing dBase with VBA

[chip_zli]

hello ...

... first of all I'd like to apologize for my bad english, I'm not from english speaking
area.

I started programming in VBA/Access2k two weeks ago, I am really low on knowledge, not just on VBA but OO programming in general because all I did
until now is C/assembly. So please be patient with me =)

I need to access .dbf (dBase) database file from VBA. My first guess was ( I can't remember syntax, but something like

Dim dbRadna As Database

Set dbRadna = OpenDatabase ("c:\artikli\dstasb.dbf", ...)

which fails with error unrecognized file format or something like that. Then I figured this thing ODBC. As I see it, it is some kind of layer between
application and databases itself(right?), so if I could access it directly( I had
to learn SQL so I can communicate with ODBC anyway) , IMHO I could reach
my goal. In win98 I went C Panel/ODBC Menager/System DNS, created new
system data source, MS dBase driver (btw what is difference between
regular and VFP driver??) , directory is c:\artikli, but OpenDatabase fails
anyway (same error).
Than I saw OpenConnection. As soon as I get to my workplace, I'll try something like

Set spoj = radpr.OpenConnection("tis", <something>, <something>)

where spoj is Connection and radpr is ODBCWorkspace, and tis is the name of data source created in CP/ODBC/SDNS. So my questions would be :

1) Can dBase files be accessed from VBA ? Since I can import them, Jet understandes them. I see no reason VBA (more precisley, DAO) can't.

2) Can it be done using OpenDatabase? ( I doubt, but ... )
3) Can it be done using OpenConnection ? ( God, I hope so =).
4) Am I looking in right direction at all or am I waaaaaay off?

So If=) anyone could help me by answering these questions, or share an advice, example or expirience, it would be greatly appriciated=) Thx in
advance.


p.s. OS = win98, access2k, dao 3.6

 

[Mile-O]

Are you trying to access the records in a table/query or manipulate the other objects of the database?

 

[chip_zli]

Are you trying to access the records in a table/query or manipulate the other objects of the database?

Access the records. In database I got like [AMOUNT];[DATE];[WHO] . All
I need is summing AMOUNT based on user-given criteria.

 

[Mile-O]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("[i]yourQuery[/i]")

With rs
    ' navigation here
    ' some example commands
    ' .MoveLast
    ' .MoveFirst
    ' .RecordCount
    ' .Find
    ' .NoMatch
    '. Fields("YourField")
End With

Set rs = Nothing
Set db = Nothing

 

[chip_zli]

Dim db As DAO.Database

-- Snip --

Set rs = Nothing
Set db = Nothing

Hmmm...we probably didn't understand each other. Using CurrentDb I accomplish nothing. You see, I have a access database (mdb) that contains only few forms from which I need to access dBase database. Those are NOT
imported, so I don't know what are you trying to suggest. But I solved the problem of accessing bases using :

---------------------------------------------
Dim wrkSpace As Workspace
Dim conOne As Connection
Dim rs As Recordset


Set wrkSpace = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conOne = wrkSpace.OpenConnection("tis", dbDriverNoPrompt, , "ODBC;DATABASE=dstasb;UID=sa;PWD=;DSN=tis")
Set rs = conOne.OpenRecordset("dstasb", dbOpenSnapshot, dbRunAsync)

With rs

... doing something

End With


rs.Close
conOne.Close
wrkSpace.Close


End Sub
------------------------------------------------

What I still don't know is how to caliculate totals with this method. Can I still use DSum, or what?

 

[Jon K]

I think it's easier if you link the dbf file as a table in Access (choose menu File, Get External Data, Link Tables...)

Then you can use the linked table in queries and DSum() etc.
.

 

[chip_zli]

I think it's easier if you link the dbf file as a table in Access (choose menu File, Get External Data, Link Tables...)

Then you can use the linked table in queries and DSum() etc.
.

Yeah, it's easier for me. But I got 8 employee who can't remember like what
Cc: in e-mail stands for. Whould you like to explain them what is linking, why they should do it and how?=)))

 

[Jon K]

With rs

... doing something

End With
------------------------------------------------

What I still don't know is how to caliculate totals with this method. Can I still use DSum, or what?

You can't use DSum on a recordset.

But since you have already opened the dbf file as a recordset, you can loop through the records in the recordset to calculate totals.
.