MS Access MDB to VFP tables

Cetin Basoz, April 26, 2001

Well it started when I found it hard to check rtf files for MSDN subscription index. In last few shipments there were also an MDB in 'UN-SUPPED' directory but my Access is too perfect to use an MDB so I decided to get Access data to VFP tables.

lcDataBaseName = 'MSDN'
lcMDBDir = 'C:\My Documents\MSDN CD SUBCRIPTION INDEXES\UN-SUPPED'
lcMDBName = 'MSDN APRIL CD SUBCRIPTION INDEX.MDB'
lnConnHandle = SQLSTRINGCONNECT('DSN=MS Access Database;DBQ='+;
  lcMDBDir+'\'+lcMDBName+';DefaultDir='+;
  lcMDBDir+';DriverId=25;FIL=MS Access;MaxBufferSize=2048;'+;
  'PageTimeout=5;UID=admin;')
Create data (lcDataBaseName)
SQLTABLES(lnConnHandle, ['TABLE'], 'myMDB')
Select myMDB
Scan
  lcSQL = 'select * from "'+trim(table_name)+'"'
  lcCursor = chrtran(trim(table_name),' ','_')
  lcTableName = '_'+lcCursor
  SQLEXEC(lnConnHandle,lcSQL,lcCursor)
  Select (lcCursor)
  Copy to (lcTableName)
  Add table (lcTableName)
Endscan
SQLDISCONNECT(lnConnHandle)
Modi data

Public oForm
oForm = Createobject('myForm')
oForm.Show()

Define Class myForm As Form
  Height = 450
  Width = 850
  DataSession=2
  Caption='Show Access Data'

  Add Object lblAccess As Label With ;
    Caption = "Access Database", ;
    Left = 10, Top = 15, Width = 100

  Add Object txtMDBlocation As TextBox With ;
    Left = 112, Top = 12, Width = 520

  Add Object cmdBrowse As CommandButton With ;
    Top = 10, Left = 640, Caption = "Browse", Autosize=.t.

  Add Object lblTables As Label With ;
    Caption = "Tables", Left = 20, Top = 40, Width = 40

  Add Object lstTables As ListBox With ;
    Height = 400, Left = 65, Top = 40, Width = 265

  Add Object grdShow As Grid With ;
    Height = 400, Left = 340, Top = 40, Width = 500

  Procedure listtables
    Local lnConnHandle,lcMDB
    With This.txtMDBlocation
      If Empty(.Value) Or !File(.Value)
        Return
      Endif
      lcMDB = Trim(.Value)
    Endwith

    lnConnHandle = ;
     Sqlstringconnect("Driver={Microsoft Access Driver (*.mdb)};"+;
                     "Uid=Admin;DBQ="+m.lcMDB)
    SQLTABLES(m.lnConnHandle, ['TABLE'], 'crsTables')
    SQLDISCONNECT(m.lnConnHandle)
    Select crsTables
    This.lstTables.Clear()
    Scan
      This.lstTables.AddItem(crsTables.table_name)
    Endscan
  Endproc


  Procedure txtMDBlocation.LostFocus
    Thisform.listtables()
  Endproc


  Procedure cmdBrowse.Click
    This.Parent.txtMDBlocation.Value = Getfile('MDB','','',0,'Select Access Database')
    Thisform.listtables()
  Endproc


  Procedure lstTables.InteractiveChange
    Local lnConnHandle,lcMDB,lcSQL
    With This.Parent.txtMDBlocation
      If Empty(.Value) Or !File(.Value)
        Return
      Endif
      lcMDB = Trim(.Value)
    Endwith

    lcSQL = 'select * from "'+Trim(This.Value)+'"'
    lnConnHandle = ;
     Sqlstringconnect("Driver={Microsoft Access Driver (*.mdb)};"+;
                     "Uid=Admin;DBQ="+m.lcMDB)
    SQLEXEC(m.lnConnHandle,m.lcSQL,'crsLocal')
    SQLDISCONNECT(m.lnConnHandle)
    With This.Parent.grdShow
      .ColumnCount = -1
      .RecordSource = 'crsLocal'
    Endwith
  Endproc
Enddefine

Cetin Basoz, Engineerica Inc.
Çetin is Engineerica's lead developer and also administering "Institute of Marine Sciences and Technology-Izmir" computer division. He specializes in using Visual FoxPro, .NET and SQL server. He is using Foxbase, Foxpro and Visual FoxPro since 80's, .NET since 2004. He has been a Microsoft MVP 1999-2010. His expertise has been used in prototyping, development, training and testing. Now he continues his carrier on VFP and C#.Net doing mostly Silverlight RIA applications. Though Çetin is well known for his programming skills, very few people know that he is also a licensed Medical Doctor. After practicing medicine for about ten years Çetin switched careers and went to his true passion - software programming.

How can I get RGB(...) value from a color number?

Cetin Basoz, September 20, 2000

This will get you the RGB equivalent from a color number. These are 2 little functions doing it different ways. You may try which one fits better your needs. The first one uses the 256 syntax to get the color. The second one is using the BITAND and BITRSHIFT approach.

Images, containers in grid

Cetin Basoz, August 9, 2000

With grids it's a little problematic to show different images or container objects per row. You might have only image filenames stored in a table or you might want to show something like a time table with shape controls where you only store start-end values as numeric. Grid shows the same thing on a...

Interacting with Word, using wordbasic

Cetin Basoz, February 16, 1998

Here is two sample functions one is just for using wordbasic and the second is using wordbasic in mailmerge. Wordbasic PEMs are defined in wrdbasic.hlp located in winword directory. This help file contains the arguments as named arguments which are not suitable for calling via VFP. In files section...

Online database backups

Cetin Basoz, January 6, 1998

Databases have a little different header from tables thus making it impossible to copy them online through SQL or "copy to". The code below accomplishes this opening the DBC readonly and creating a copy of it using lowlevel IO.

Using the Treeview Control

Cetin Basoz, June 1, 2001

Introduction Any data that could be represented in an hierarchical way is a candidate for a treeview listing. This article will show you how to use the MS Active X treeview control in your own applications in a simple way, populate it fast and apply drag&drop to it. Note that this control is ...