Insert Failure

[Deltaflyer]

I am trying to insert a row into an Access table, but i keep getting this error,

Run-Time Error 3011

The microsoft jet db engine could not find the object 'insert into details (wkstkarea), ('DBArea1')'.

Here id my coding :
Dim db As Database
Dim rs As Recordset
Dim sql As String

Open App.Path + "\dblocation.fil" For Input As #1
Line Input #1, dblocation
Close #1

sql = "insert into details (wkstkarea), ('" & Combo1.Text & "')"

Set db = Workspaces(0).OpenDatabase(dblocation+ "\vbft.mdb&quot
Set rs = db.OpenRecordset(sql, 1)

rs.Close
Set rs = Nothing


Any ideas what wrong with it? DeltaFlyer
The Only Programmer To Crash With Style. LOL

 

[MichaelRed]

Wheather you "crash w/ Style" or not I leave for others to vote on. It is obvious that you do whatever you do with LOTS of "Color". But then you already know that, and are interested in the problem.

It is way more colorful than I am accustom to, but I think the sql string doesn't resolve to a cohernet (bland? colorLESS?, LEGAL?) statement.

Try copying the line into the debug window, and attempt to execute it. It SHOULD return a string -but yields an error instead.

I modified the assignmnet statement:

sql = "insert into details (wkstkarea)," & "(" & "Combo1.Text" & &quot"

to Return:
insert into details (wkstkarea),(Combo1.Text)

Which I am sure is not what you are after.

COGITO you are attempting to achieve:

sql = "insert into details (wkstkarea), (" & Chr(34) & "Combo1.Text" & Chr(34) & &quot"
? Sql
insert into details (wkstkarea), ("Combo1.Text&quot


However this is not a complete (legal) sql statement, and I cannot devine the remainder.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Deltaflyer]

My style is big bold brash and yep you guessed it colo[color]orange]urful, and i like to crash my machine a lot. (I code it in to most of my programs just in case some nosey so&so wants to nuke the database behind it.)

I colour code everything this makes it a lot easier for training, have you not noticed that people tend to learn faster if things are pleasant. Windows Battle Ship Grey is not pleasant.

Anyway, just my opinion. Back to the problem,

insert into details (wkstkarea), ('DBC&A_css_rpt')

This is the actual sql statement that is being passed to the OpenRecordset command, i see nothing wrong with this.

Is there something that i have missed?

DeltaFlyer
The Only Programmer To Crash With Style. LOL

 

[Deltaflyer]

My style is big bold brash and yep you guessed it colourful, and i like to crash my machine a lot. (I code it in to most of my programs just in case some nosey so&so wants to nuke the database behind it.)

I colour code everything this makes it a lot easier for training, have you not noticed that people tend to learn faster if things are pleasant. Windows Battle Ship Grey is not pleasant.

Anyway, just my opinion. Back to the problem,

insert into details (wkstkarea), ('DBC&A_css_rpt')

This is the actual sql statement that is being passed to the OpenRecordset command, i see nothing wrong with this.

Is there something that i have missed?

DeltaFlyer
The Only Programmer To Crash With Style. LOL

 

[MichaelRed]

Are the single quotes part of the field value you expect to see in the TABLE? Probably not - but that is not thte real problem. Obviously, the "system" is seeing the sql text as the NAME of a recordset - and cannot find any such recordset (either as a TABLE or a Query DEF).

The syntax (Workspaces(0)) is reminicent of Ms. Access 2.0, which is several generations removed from this particular set of brain cells (and devilishly difficult to resurrect!), but I think that the syntax for such was nore along the lines of DoCmd.RunSql sql or Execute.Sql The OPenRecordset method requires that the SLQ statement 'returns records', which an "Insert" does NOT.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Deltaflyer]

Could you give me a working example of what you mean, cos i've not used vb properly since the hey-days of vb4, so i'm not that versed on VB6. I know how to make it do most things but accessing a DB is not my forte.

Thanks for help so far, little more and ill be eternally grateful. DeltaFlyer
The Only Programmer To Crash With Style. LOL

 

[MichaelRed]

It depends on Which ver you are using for both Ms. Access and VB. Ver5 & 6 of VB are quite similar, but there are differences in the db mechanics evem there. MS Access 2 is more-or-less totally out of sync with everthing except itself. ver '95 & '97 are almost the same (for most users), but ver 2K defaults to ADO data access - which is at least a whole step.

In general, you are probably going to want to do the DAO data access, where Dbengine (0 is replaced w/ CurrentDb. To actually do an Insert query in Code, you will want to use a QueryDef Object and and execute.

Beynd that, I'm pretty unsure which branch in the road you will go down. I would advise you to get third party books on bot VB and Ms. Access - ones that cover whatever versions you are using and at least through VB 6 and Ms. Access ver 2K.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jjames]

I'm not sure but would the execute method work for inserting a row?

db.Execute(sql)