Writing to dBase III table

[bar]

I'm trying to add records to a dBase III table, using ADO and ODBC. The code runs fine, without any errors, but the record never gets added. Any ideas or suggestions would be appreciated.

[dcarlson]

How are you adding to the table? Control or SQL statement?

[bar]

Dim cnCompLit As ADODB.Connection
Dim rsCompLit As ADODB.Recordset

Set cnCompLit = New ADODB.Connection
Set rsCompLit = New ADODB.Recordset

cnCompLit.Open "DSN=CompLit"
rsCompLit.Open "CompLit", cnCompLit, adOpenDynamic, _ dbLockOptimistic, adCmdTable

rsCompLit.AddNew
rsCompLit("Surname") = txtStudentName.Text
rsCompLit("Initials") = Left(txtFirstName.Text, 1)
rsCompLit("Login") = txtStudentNo.Text
rsCompLit("Password") = txtStudentNo.Text
rsCompLit("Grade") = "1"
rsCompLit("ID") = txtStudentNo.Text
rsCompLit.Update

This is the code that I run. It works fine on any Access or SQL database but not DBase III.

[dcarlson]

Use this to see if your recordset supports the addnew method.

booSupports = rsCompLit.Supports (adAddNew)

If it returns false, which I think it will, you'll have to use an SQL statement such as.

strSql = "INSERT INTO ...."

cnComplit.Execute (strsql, , adcmdtext)

rsCompLit.Requery

This is what I have to do with FoxPro, which is an XBase database.

Hope this helps.

[/code]

[gerard]

Hi guys...

Had the sme type of problem updating a dBase 5 table. After some research I now believe that you can't!!!! use ADO to update dbase tables..it seems that microsoft doesn't support it. you can run select statements over it but you can't use any action type queries.

Solution: Go back to using DAO 3.51 I have done this with no grief! If you do find a way with ADO let me know. If you need example coding for this give me a yell!

Gerard

[TimCottee]

Updating DBase with the Jet 4 engine has been disabled in data access components 2.5. However it is still feasible to use the Microsoft Visual Foxpro Driver to update dBase tables using ADO.

cnCompLit.ConnectionString = "Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=<Path To .dbf Files>;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

This should allow you to correctly update the tables.

[bar]

Thankyou for all the help guys. After a lot of reading I came across the following in the Knowledge Base:
A Dbase table cannot be updated if the Borland Database Engine is not installed.

So I installed the engine and now it works just fine with the original code.

[dcarlson]

One thing I love about this forum is that you find out things that you never knew.

Usually when I open a recordset I always use a query string and you can't update the recordset. I never tried opening a recordset as a table and updating it but it works well.

Thanks TimCottee