Runtime Error 3251 - HELP!!

[Thom]

Help! I'm trying to dump a CSV textfile into my Access97 database, using ADO. when I get to the rs.AddNew line I get Error 3251! (The operation requested by the application is not supported by the provider) Here's the code:

Set db = New ADODB.Connection
Set rs = New ADODB.Recordset

connectString = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=" & App.Path & "\TestDB1.mdb;Jet OLEDB:database password=;Mode=Share Deny None"

' NOW OPEN DB
db.Open connectString

' NOW the Recordset
rs.Open "FirstTable", connectString, , , adCmdTable

' OPEN THE TEXT FILE
Open App.Path & TextFileName For Input As #1

'WRITE TEXT FILE TO VARIABLES
Do While Not EOF(1)

Input #1, sFirstField, sSecondField

' NOW LOAD THE DB

rs.AddNew ** HERE'S WHERE THE ERROR POPS UP!!
rs!FirstField = Trim(sFirstField)
rs!SecondField = Trim(sSecondField)
rs.Update

WHAT am I missing?? I'd appreciate any help! NOTE: the Access DB is a simple 1 table(FirstTable) DB, NOT password protected, if that helps any.

[pardede]

Maybe you didn't open the recordset correctly:

Your code:
rs.Open "FirstTable", connectString, , , adCmdTable

Try use:
Set rs = db.OpenRecordset "FirstTable"

If I'm not mistaken, your syntax opens a disconnected recordset... ?

[LG]

I think, instead of
rs.Open "FirstTable", connectString, , , adCmdTable

you have to write:

' NOW the Recordset
rs.Open "FirstTable", db, , , adCmdTable

And you don't have to put ADO control on a form.

[Thom]

Thanks for the suggestion LG! I typed in EXACTLY what you said, but I STILL get the same damn error!!! Maybe I need to do it in DAO, but I wanted to avoid that! Well, I'll keep playing with it...

[LG]

Try to add CursorType and LockType before you open recordset.

rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "FirstTable", db, , , adCmdTable

[Mongo]

I did a quick search of MSDN and found this. Hope it helps.

PRB: Error 3251 Generated by Calling ADOX Methods
Article ID: Q201716

The information in this article applies to:
ActiveX Data Objects (ADO), version 2.1

SYMPTOMS
Executing an ADOX method such as the following:
Catalog.Tables.Append tbl
against SQL Server may generate the following error:

Run time error "3251": "The operation requested by the application is not supported by the provider".

CAUSE
SQL Server's Stored Procedures are not current.

RESOLUTION
Run the Instcat.sql script that came with the version of Microsoft Data Access Components (MDAC) currently installed.

[Thom]

Yes!! LG, your latest worked!! Thanks so much! Out of curiosity, any idea WHY we have to go thru all those permutations to get the sucker to work right?? I thought ADO was supposed to be EASIER to use <g>!

And Mongo, have you run the script on your system-and did it work? Thanks for all y'alls help!!

[LG]

I don't know why. But I ran at the same problem using AddNew with DAO and found out, that it is a bug.You have to specify all parameters in Openrecordset. So I assumed, that it's the same problem.
Glad it helped.

[Eduardo Freitas]

Before

...
rs.Open "FirstTable", db, , , adCmdTable
...

Set your connection location

...
.CursorLocation = adUseClient
...