I'm going to be transferring data from a SQL database to an MDB. I can
create an empty access MDB file using the connection wizard, and once I
have that, it's not difficult to do the transfer.
For reasons of my own, nothing critical, I was just wondering if there is
a way to programattically create a new MDB file using only vfp commands
instead of the wizard?
Here's a few lines of DAO code I've used (thanks to VFP guru Eric den
Doop). It should be enough to get you started, though I imagine there are
some differences in later versions of Access.
thefile = "C:\Some Path\SomeFile.mdb"
thetbl = "TableInsideAnMDBFile"
* The version of Access in Office 97. Throws Error 1733 if the engine isn't
installed on the computer.
oDaoEngine = CREATEOBJECT("dao.dbengine.35")
* I imagine this is like VFP Work Areas.
oWorkSpace = oDaoEngine.WorkSpaces(0)
* Create the .mdb database file.
oDatabase = oWorkSpace.CreateDatabase(thefile,dbLangGeneral)
* Define a table.
oTable = oDatabase.CreateTableDef(thetbl)
Here I do a loop on an AFIELDS() array, adding the fields from a VFP table
to the Access table. You may have to cast some VFP data types to Access
types as you go.
* This appears to define the field properties.
oField = oTable.CreateField(fieldname,datatype,fieldwidth)
* This appears to actually add the field to the table.
oTable.Fields.Append(oField)
* I'm not sure what this does; it may allow nulls? (It was in den Doop's
original code.)
oTable.Fields(fieldname).AllowZeroLength = .T.
Once you've finished the loop:
* Add the newly-defined table to the database.
oDatabase.TableDefs.Append(oTable)
oDatabase.TableDefs.Refresh()
Now you can add the data:
* Define a recordset.
oRecordSet = oDatabase.OpenRecordSet("SELECT * FROM " + thetbl)
Next SCAN your VFP table, and...
* Add a new record to the Access table.
oRecordSet.AddNew()
Loop through the fields and...
oRecordSet.Fields(fieldname) = [value stored to current field]
After getting everything from one row into the table:
oRecordSet.Update()
After the SCAN...ENDSCAN
* Close the DAO recordset.
oRecordSet.Close()
* Close our new .mdb database.
oDatabase.Close()
Ken Dibble
www.stic-cil.org
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
