Working with Access 2002 tables programmatically... 1

[thatguy]

Hey there folks--

I'm trying to make a switch to Access (2002) from FoxPro and am having a LOT of trouble figuring out how to work with tables directly through VBA. Specfically, when a new record is created, I need to search through the table, find the largest value in a text field, increment it and set the new record's field to that value (not an AutoNumber) -- example: the largest value would be Q030401A.. the next value would be Q030401B).

Can someone give me a brief (or detailed) run-down of how to work with tables directly thru VBA? I've searched for tutorials, but most talk about a var type of Database, which is undefined in Access 2002... I'm confused. Help?

Thanks
-- michael~

 

[Omadmax]

I am having the same problem. Everwhere I look I see code fragments as such;

Dim testdb As Database.

When I put this in my application I get an error stating that Database is a user defined type and is not defined.

Does anyone have any information on why is is occuring.

 

[Trendsetter]

It's like most things - it's simple once you can do it...
But at times we all fall off!

to open a table for working with you need the following:

Dim rs as Recordset 'means Declare rs as a variable
Set rs = CurrentDB.OpenRecordSet("tblStaff&quot 'means use the rs to expose the table of staff for me to play with

now can use any of the following
rs.edit
rs.AddNew
...........
I have extracted a piece of code from one of my apps for you to look through if you need any further help come back to me:

Private Sub cmdUpload_Click()
Dim Num As Integer
Dim dynTable1 As Recordset
Set dynTable1 = CurrentDb.OpenRecordset("tblAllData", dbOpenDynaset)
Dim dynTable2 As Recordset
Set dynTable2 = CurrentDb.OpenRecordset("tblTempData", dbOpenDynaset)
dynTable2.MoveLast
Num = dynTable2.RecordCount 'number of records in the table
dynTable2.MoveFirst 'back to top of table
MsgBox "There are: " & x & " records to be added!"
Do Until dynTable2.EOF
dynTable1.AddNew
dynTable1!UniqueRef = dynTable2!UniqueRef
dynTable1!Priority = dynTable2!Priority
dynTable1!StartDate = dynTable2!StartDate
dynTable1!IssueThreat = dynTable2!IssueThreat
dynTable1!PropAction = dynTable2!PropAction
dynTable1!DueDate = dynTable2!DueDate
dynTable1!Actionee = dynTable2!Actionee
dynTable1!ActionTaken = dynTable2!ActionTaken
dynTable1!Complete = dynTable2!Complete
If Len(dynTable2!UniqueRef) = 14 Then
dynTable1!WorkshopID = Left(dynTable2!UniqueRef, 10)
Else
dynTable1!WorkshopID = Left(dynTable2!UniqueRef, 11)
End If
dynTable1!CompDate = dynTable2!CompDate
dynTable1.Update
dynTable2.MoveNext
Loop
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblTempData"
DoCmd.SetWarnings True
dynTable1.Close
Set dynTable1 = Nothing
dynTable2.Close
Set dynTable2 = Nothing
DoCmd.Close
End Sub

 

[thatguy]

You're right... that Is pretty simple! lol

I suppose things get a bit more complicated when dealing with two separate databases at once? Suppose you would need to copy selected fields/records from one database to another, would it be easier to link in the old table... and if you did that, could you work with that linked table as if it were in the currentdb?

Another question -- suppose you have a table opened and sorted on a particular field in a form, would setting a recordset var to the same table, but sorting by a different field, affect the form table at all? ... other words: is the table referenced by the recordset var the same one that the form uses, or is it a second instance of the same table? Make sense?

Thank you very much for the help!
-- michael~

 

[Trendsetter]

A linked table is just the same as a local table - all of my table are linked tothe front end application.

The above code utilises 2 tables independantly in the form of dynTable1 & dynTable2.

When a form accesses a table or sets of tables it creates a recordset via the SQL developed by the query - when a recordset is opened via code it again generates a recordset which will be independant of other recordset - consequently you can get write locking error in a multi user environment.

Helpful??

 

[thatguy]

Yes, very helpful -- thank you!

However---- I finally got around to trying out the above code and for Some reason, I'm getting data type mismatch when I try to OpenRecordset?!? Here's the code:

Dim rst As Recordset
Dim lnrecc As Integer

Set rst = CurrentDb.OpenRecordset("category&quot
This is where I get the type mismatch. Any idea why??

lnrecc = rst.RecordCount
Debug.Print lnrecc

rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst!camount
rst.MoveNext
Loop

rst.Close
Set rst = Nothing


Thanks again for the help
-- michael~

 

[Trendsetter]

Is Category a table or a query???

I have not had this problem on a table but I have on a query where the query had a WHERE clause which referenced a string --- it then needed the WHERE clause modifying to:

WHERE "FormName=" & "'" " Me!SomeControl & "'"

 

[thatguy]

nope, Category is just a table... ??

 

[thatguy]

ARGH! I just tried putting the dbOpenDynaset argument in there and it tells me the variable is not defined.. Do I not have something installed?!

-- michael~

 

[Trendsetter]

It does not recognise dbOpenDynaset and is treating it as a variable which should be declared .. Dim etc..

Are you using DAO or ADO???

 

[thatguy]

*shrug* Where would I specify that?

 

[thatguy]

Got It! I didn't have the DAO 3.6 checked in the tools->references list.. everything's working fine now -- thank you very much for the help!

-- michael~