Empezando con ADO

[Antonio Linares]

Rick,

I am using ADO (Jet) and Access for several tests.

Thanks for the URL to update Jet, I will try it on such laptop

[Rick Lipkin]

Cristobal,

Now that you know how to create and open an Access table .. you can easily convert .dbf to .mdb

Rick Lipkin

Code: Select all  Expand view

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

   // check for very first user

cSQL := "SELECT * FROM USERINFO"
TRY
    oRs:Open( cSQL, xCONNECT )  // you can use the FW_function Antonio mentioned above as well
CATCH oErr
    Saying := "Error in Opening USERINFO table to"+chr(10)
    Saying += "Create the First Admin User"+chr(10)
    MsgInfo( Saying )
    oCn:Close()
   oDlg:End()
   RETURN(.F.)
END TRY

cLOGIN := UPPER( WNetGetuser() )+space(25) // fivewin
cLOGIN := SUBSTR(cLOGIN,1,25)

If file( cDefa+"\Userinfo.dbf" )

      cSay := "Importing Legacy Users "
      oSay:ReFresh()

      Select 1
      If NetUse( cDefa+"\Userinfo.Dbf",.t.,5)
         Set Order to tag Userid
      Else
         oCn:Close()
         oDlg:End()
         Return(.f.)
      Endif

      nEid := 1

      Select UserInfo
      Go Top

      Do While .not. Eof()

         cSay := "Importing Legacy Users "+UserInfo->UserId
         oSay:ReFresh()
         SysReFresh()

         If Userinfo->UserId = "  "
            Select UserInfo
            Skip
            Loop
         Endif

         cEid := StrZero(nEid,18)

         oRs:AddNew()
         oRs:Fields("UserEid"):Value   := cEid
         oRs:Fields("UserId"):Value    := substr(UserInfo->UserId+space(25),1,25)
         oRs:Fields("Lname"):Value     := substr(UserInfo->Lname+space(15),1,15)
         oRs:Fields("Fname"):Value     := substr(UserInfo->Fname+space(1),1,1)
         oRs:Fields("ReadOnly"):Value  := if(UserInfo->Read  = " ", "Y", UserInfo->Read)
         oRs:Fields("WriteOnly"):Value := if(UserInfo->Write = " ", "N", UserInfo->Write)
         oRs:Fields("Super"):Value     := if(UserInfo->Super = " ", "N", UserInfo->Super)
         oRs:Fields("Admin"):Value     := if(UserInfo->Admin = " ", "N", UserInfo->Admin)
         oRs:Fields("EntryBy"):Value   := if(UserInfo->EntryBy = " ", "Import", UserInfo->EntryBy)
         oRs:Fields("EntryDate"):Value := if(UserInfo->EntryDate = ctod(""),;
                                             dtoc(Date())+" "+Time(),UserInfo->EntryDate )
         oRs:Fields("LastLog"):Value   := UserInfo->LastLog
         oRs:Fields("Password"):Value  := Space(16)
         oRs:Fields("UpDated"):Value   := if(UserInfo->Updated = 0, 1, UserInfo->Updated )
         oRs:Update()

         nEid++
         Select UserInfo
         Skip

      Enddo

      Close Databases
Endif

 

[cnavarro]

Mr. Rick
This is also a breakthrough for me
As I believe the indices from the. Ntx present some example?
thanks

Esto tambien es un gran avance para mi
Como creo los indices a partir de los .Ntx actuales, algun ejemplo?
Gracias

[Rick Lipkin]

Cristobal,

The index on the .dbf is irrelevant .. you can just open the database in natural order and use the above code to migrate the data to your Sql table. Be sure that you have Set Deleted On so you won't process any deleted .dbf records... or you can put an if deleted() statement

Code: Select all  Expand view

Select Userinfo
Go top
DO while .not eof

     If deleted()
        select Userinfo
        skip
        loop
     endif

...  recordset code migration as above
...

    select userinfo
    skip

EndDo
 

Rick Lipkin

[cnavarro]

Mr. Rick
Cuando he creado la base de datos .mdb, ¿cómo creo los ficheros indices de la nueva base de datos?

Mr. Rick
When I created the database. MDB, how do I create indexes files of the new database?

[Rick Lipkin]

Cristobal,

Indexes in Sql and Indexes using .dbf are totally different. I do not use any indexing with Ms Access ( not available in ms sql server ) .. when you write Sql statements you craft the script to fetch a certain set of rows based on your Select statement...( filters as well ) and SORT with the Order By clause.

Lets say you want to fetch a set of rows from a Customer table just for the State of South Carolina or 'SC' ..

Code: Select all  Expand view

cSql := "Select * from [Customer] where [State] = 'SC'"

or

cState := "SC"
cSql := "Select * from [Customer] where [State] = '"+cState+"'"

or with a filter condition .. sorted by

cState := "SC"
cSql := "Select * from [Customer] Order by [State],[City]"

oRs:Filter := "[State] = '"+cState+"'"

 

Sql is a different mindset than .dbf .. but once you understand the principals .. it is an easy transition. A post Script .. I use the brackets[] to delineate tables and fields. For me it is just easier to read and you will find some Sql 'key words' that may cause some confusion in your script that may need to be surrounded with brackets to process correctly... especially if spaces are used in the table and or field names.

Rick Lipkin

[cnavarro]

Mr. Rick
Mi error es recordar cuando hace mucho tiempo usaba Access y despues mis comienzos con Mysql, que utilizaba "primary keys" y campos indices.
Mi agradecimiento por su tiempo y paciencia.

My mistake is to remember when used long ago and after my early Access to Mysql, which used "primary keys" fields and indexes.
My thanks for your time and patience.

[cnavarro]

Dos preguntas:
- La function FW_CreateMdb, no la encuentra.
A partir de que version está incluida?
- Para cerrar la conexion es oCon:Close() ?

Two questions:
- The function FW_CreateMdb, not getting it.
   From that version is included?
- To close the connection is oCon: Close ()?

A la primera contesto yo:

The first answer I:

FWH 13.05

No?

[Rick Lipkin]

Cristobal,

The Ado FW functions are only in the last few recent versions .. To CLose a connection it is oCn:Close().

Rick Lipkin

[cnavarro]

Rick,
He conseguido crear la base de datos
Ahora intentare crear las tablas.
Espero poder actualizarme a la nueva version pronto.

I managed to create the database
Now will try to create the tables.
I hope to upgrade to the new version soon.

Code: Select all  Expand view

Function UsarAcces()
Local oCon
Local cBase := "archivo.mdb"
Local oCatalog

  if ! File( cBase )
     if MsgYesNo( cBase+" no encontrada" + CRLF + "Quieres crearla ?" )
       
        oCatalog := CreateObject("ADOX.Catalog")
     
        oCatalog:Create("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type = 5 ;Data Source="+cBase)
     
        oCon = TOleAuto():New( "ADODB.Connection" )      
        oCon:Open( "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source='archivo.mdb';" )
     
        MsgInfo( "Cerrar" )
        oCon:Close()
     
     endif
  else
     
     MsgInfo("La Base de datos EXISTE")
     // Abro la base de datos
     oCon = TOleAuto():New( "ADODB.Connection" )      
     oCon:Open( "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source='archivo.mdb';" )
     
     MsgInfo( "Cerrar" )
     oCon:Close()
 
  endif
 

Una pregunta:
One question:

Jet OLEDB:Engine Type = 5

es necesario porque uso Jet.OLEDB.4.0 ?

is necessary because Jet.OLEDB use. 4.0 ?

[Rick Lipkin]

Cristobal,

From the Microsoft Website ...
http://msdn.microsoft.com/en-us/library ... 10%29.aspx

Rick Lipkin

Jet OLEDB:Engine Type A Long value (read/write) that specifies which Jet database engine I-ISAM driver to use to access this database or file format. When you create a new database by using the Create method of the ADOX Catalog object, this can be used to specify the format for the new database. Once a database has been opened, this property can be read to determine what file version or format is open.

The Jet OLEDB:Engine Type property can be any of the following values:

Engine Type Setting

Unknown 0
Microsoft Jet 1.0 1
Microsoft Jet 1.1 2
Microsoft Jet 2.0 3
Microsoft Jet 3.x 4
Microsoft Jet 4.x 5

[cnavarro]

Rick, gracias

[cnavarro]

[ESPAÑOL]
Buenas noches

Ya he avanzado un poco en el tema, pero no se como solucionar los siguientes temas:

He conseguido llegar a poder seleccionar y abrir una Tabla:

oMdbTable := AdoOpenTable( oCon, cTblMdb )

1.- Lo he hecho con .Schema(20), hay otra forma?, puede servir ADOX.Table?

2.- ¿Cómo puedo cerrar la tabla sin tener que cerrar la conexion?

3.- Estoy investigando cual es el comando ADO que realiza la funcion AdoOpenTable ( dónde encuentro su código? )

4.- Cómo puedo saber la estructura de la tabla activa?
Creo que es con el ADODB.RecorSet y ejecutando una sentencia a través de ADODB.Command
pero eso es ya bastante complicado para mí. Hay otra forma?

5.- Cuáles son los tipos de campos más habituales para su importación de DBF ?, ya que he estado leyendo y hay muchos tipos de campos en ACCESS

Disculpen tantas preguntas
Adjunto una imagen de mis "progresos"

[ENGLISH]
Good night

I've made ​​a little progress on the issue, but not how to solve the following topics:

I managed to get to be able to select and open a table:

     oMdbTable: = AdoOpenTable (oCon, cTblMdb)

1. - I've done it. Schema (20), there is another way?, Can serve ADOX.Table?

2. - How I can close the table without having to close the connection?

3. - I'm investigating what ADO command that performs this function AdoOpenTable (where I find your code?)

4. - How I can know the structure of the active table?
      I think it's with ADODB.RecorSet and executing an ADODB.Command through
     but it is already quite complicated for me. Is there another way?

5. - What are the most common field types for import of DBF?, Since I've been reading and there are many types of fields in ACCESS

Sorry for so many questions
Attached is a picture of my "progress"

[cnavarro]

Bueno, he podido resolver algunas de las dudas que tenia:

He conseguido llegar a poder seleccionar y abrir una Tabla:
1.- Lo he hecho con .Schema(20), hay otra forma?, puede servir ADOX.Table?

4.- Cómo puedo saber la estructura de la tabla activa?
Creo que es con el ADODB.RecorSet y ejecutando una sentencia a través de ADODB.Command
pero eso es ya bastante complicado para mí. Hay otra forma?

Si, en este post se desarrolla como ver las tablas y la estructura de una tabla ( pero solo el nombre del campo )

Code: Select all  Expand view

        cTabla      := oCat:Tables( i ):Name
        cTipo       := oCat:Tables( i ):Type
 

viewtopic.php?f=3&t=11902&p=58854&hilit=ADOX.table#p58854
( Gracias Enrico )

Pero, no sé cómo saber el tipo de campo, longitud, decimales, etc

Cuando pongo por ejemplo:

Code: Select all  Expand view

               ? oCat:Tables( i ):Columns( j ):Name       // Devuelve bien el nombre del campo
               ? oCat:Tables( i ):Columns( j ):Type        //  Devuelve siempre 130    ¿?
 

Qué significa el 130 ?

ENGLISH

Well, I could answer some of the questions I had:

I managed to get to be able to select and open a table:
1. - I've done it. Schema (20), there is another way?, Can serve ADOX.Table?

4. - How I can know the structure of the active table?
I think it's with ADODB.RecorSet and executing an ADODB.Command through
but it is already quite complicated for me. Is there another way?

If, in this post develops as view tables and the structure of a table (but only the field name)

Code: Select all  Expand view

         cTabla := oCat:Tables(i):Name
         cType  := oCat:Tables(i):Type
 

viewtopic.php?f=3&t=11902&p=58854&hilit=ADOX.table#p58854

But I do not know how to know the field type, length, decimals, etc.

When I put for example:

Code: Select all  Expand view

                ? oCat:Tables(i):Columns(j):Name / / Returns either the field name
                ? oCat:Tables(i):Columns(j):Type / / Returns always 130?
 

What 130?

[cnavarro]

Qué significa el 130 ?

Un poco mas de luz
Listado de codigos de tipo de campo

A little more light
List of field type codes

Code: Select all  Expand view

#define adEmpty             0
#define adTinyInt          16
#define adSmallInt          2
#define adInteger           3
#define adBigInt              20
#define adUnsignedTinyInt   17
#define adUnsignedSmallInt 18
#define adUnsignedInt       19
#define adUnsignedBigInt    21
#define adSingle               4
#define adDouble               5
#define adCurrency          6
#define adDecimal          14
#define adNumeric         131
#define adBoolean          11
#define adError               10
#define adUserDefined     132
#define adVariant          12
#define adIDispatch             9
#define adIUnknown         13
#define adGUID             72
#define adDate              7
#define adDBDate             133
#define adDBTime             134
#define adDBTimeStamp     135
#define adBSTR              8
#define adChar            129
#define adVarChar         200
#define adLongVarChar     201

#define adWChar              130

#define adVarWChar        202
#define adLongVarWChar    203
#define adBinary             128
#define adVarBinary           204
#define adLongVarBinary   205
#define adChapter         136
#define adFileTime         64
#define adPropVariant     138
#define adVarNumeric      139
#define adArray             8192
 

Pero aun no se su equivalencia con los campos habituales usados en DBF: long, decimals

But not even its equivalence with the usual fields used in DBF: long, decimals

http://www.w3schools.com/ado/ado_datatypes.asp

Donde puedo buscar mejor informacion?

Where I can find better information?