The easiest Question about recordsets

Hi there

This will be an easy question (promise)

I have a database on Access 2002, and in my first code that I was developed I used an external table file, and a recordset, now I transfer this table into my actual project so I just want to use the table (same name) that are on the MBD file that I work...

this is my actual code

strSQL = "SELECT * FROM tblTarifas WHERE tblTarifas.Fecha = #" & strDate & "# AND tblTarifas.tblTarifaId_ = 13 AND tblTarifas.tblZonaId_ = 1"

Set myCOnn = New ADODB.Connection
myCOnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\DAVIDG\Escritorio\arreglo6\Data.mdb;"
myCOnn.Open

Set rs = myCOnn.Execute(strSQL)


and I want to change to something like that



set rs = thisdatabase.Execute(strSQL)



I remember that it was a simple comand like the above but I dont remember fine :S I have Principles of Alzhemier :( I think

Set rs = myCOnn.Execute(strSQL)


Hello Iceman Solope,
Instead of the above approach, you can use

rs.Open strSQL,AdOpenForwardOnly,AdLockReadOnly


It is better to use your ADO Connection Object .Execute Method in performing DML Commands (INSERT,DELETE and UPDATE). :)

What you are willing to do (Database.execute) is possible with DAO. Using ADO, there's no Database object, instead you must use the connection object like you are doing right now.

One thing you could do to get rid of the open statement is open the connection at the beginning of your project and close it at the end. This way you could use "Connection.Execute" whenever you want since the connection object will be set and open.

Personnaly I prefer opening and closing connection when I need them, but this is up to you.

Yes, I tried to do what saying

But in order to use the database that are on the same file that Im working, I don't want to call an external MDB file.

for example in this code:

Set conDatabase = New ADODB.Connection
conDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\DAVIDG\Escritorio\new6\Data.mdb;"
conDatabase.Open


I don't want to use an external MDB file, so I want to use the tables that are in the same file that Im working, I remember a statement "this database" but I don't remember how can I use it :s

Any suggestions I'll appreciated it

CurrentProject.Connection :)

Edited to correct

YES! That is... but I try to change the project above with this statement and it doesn't work,

In order to do less I try to change this statement


myCOnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\DAVIDG\Escritorio\new6\Data.mdb;


but when I try to do the execute comand it send me an error, because the file Data.mdb that is the same that Im working is opened and it have a write protection.

The question is still open, I simply try to change this statement


strSQL = "SELECT * FROM tblTarifas WHERE tblTarifas.Fecha = #" & strDate & "# AND tblTarifas.tblTarifaId_ = 13 AND tblTarifas.tblZonaId_ = 1"

Set myCOnn = New ADODB.Connection
myCOnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\DAVIDG\Escritorio\arreglo6\Data.mdb;"
myCOnn.Open

Set rs = myCOnn.Execute(strSQL)


in order to use my currend mdb file and not an external file,

any suggestion in order to solve this easy problem I'll appreciated it.



CurrentProject.Connection :)

Edited to correct

Can you post the latest version of the code. I'd need to see how you are using CurrentProject.Connection.

Yes HardCode

here is:



Set conDatabase = CurrentProject.Connection
strSQL2 = "Insert into tblTarifas (Fecha, Demanda, Punta, Intermedia, Base, Semipunta, Energía, [Demanda Reservada], [Demanda Medida], [Demanda Facturable], tblZonaId_, tblTarifaId_) Values (#" & strDate2 & "#, " & Me.txtActActuOMBCDem.Value & ",0,0,0,0," & Me.txtActActuOMBCEne.Value & " ,0,0,0, 1, 13)"
conDatabase.Execute strSQL2


But when I execute this method it doesn't update anything

Thnks for post :)

Please clear something up for me first:

Are you using Visual Basic 6 with an MDB file, or are you using VBA inside of Access?

I have solved this problems, with ur help

Thnks :)

David

YES! That is... but I try to change the project above with this statement and it doesn't work,

In order to do less I try to change this statement


myCOnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\DAVIDG\Escritorio\new6\Data.mdb;


but when I try to do the execute comand it send me an error, because the file Data.mdb that is the same that Im working is opened and it have a write protection.

The question is still open, I simply try to change this statement


strSQL = "SELECT * FROM tblTarifas WHERE tblTarifas.Fecha = #" & strDate & "# AND tblTarifas.tblTarifaId_ = 13 AND tblTarifas.tblZonaId_ = 1"

Set myCOnn = New ADODB.Connection
myCOnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\DAVIDG\Escritorio\arreglo6\Data.mdb;"
myCOnn.Open

Set rs = myCOnn.Execute(strSQL)


in order to use my currend mdb file and not an external file,

any suggestion in order to solve this easy problem I'll appreciated it.

Hi
To use the current mdb file in the folder where your vbforms are located use the following string
myCOnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &app.path&"\Data.mdb"
Regards
Justus