Writing Access MDB from VFP 9.0
Murray
tables & cursors I'm working with. After doing some Web searching, I
found the following advice on Foxite.com:
If you want to handle the transfer manually or programmaticlly,
install an empty MDB in your system. Connect to it with CREATE
CONNECTION or SQLCONNECT(). You can then create the tables with pretty
much the very same code you would use in VFP, that is "CREATE TABLE
..." and "ALTER TABLE ..", and normal SQL commands INSERT INTO, DELETE
FROM, SELECT .. FROM .., and UPDATE table .. , using SQLEXEC() or
using "remote views". A remote view makes the tables, in any standard
ODBC-connected database, accessible as if they were DBF tables. After
filling the view with your DBF data, which is easily done with APPEND
FROM or INSERT INTO, the data is moved to the MDB with a simple
TableUpdate() call.
It's clearly documented in the Help for VFP, and has been available
since the very first version of VFP.
-Anders
I created an empty MDB as instructed and got the connection to it. I
tried the above instructions in as many different ways as I could and
got no results. The SQLEXEC() returned no error but none of the
commands I programmed did anything. Then in Access I created a table
in the MDB and tried to do things with it but also got no results.
I began to wonder if there is a special way to identify the table in
the SQLEXEC() command as being part of the MDB but I have no idea
where to begin experimenting.
I've been working on this project for a few years now and seem to have
hit a dead end with this problem! Any ideas or thoughts would be
greatly appreciated.
Thanks,
Murray
tom knauf
1) you can use a remote view to the MDB instead of sqlexec,
2) in sqlexec we do like this :
cMdb = "d:\kundata\2005.MDB"
cdbf = "d:\kundata\2005.DBF"
cconn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + cmdb +
";Exclusive=1;Uid=admin;Pwd="
nConn=Sqlstringconnect(cconn)
? cMdb
? cdbf
If nConn>0
? SQLExec(nConn,'Select * from accesstablename','dummy2')
Select dummy2
Copy To (cdbf)
SQLDisconnect(nConn)
Endif
So you connect to the MDB, but sqlexec to the table(s)
HTH
Tom
"Murray" <mur...@chicago.com> schrieb im Newsbeitrag
news:if4755t9gmanfk02c...@4ax.com...
Murray
It looks to me like your example selects data from the MDB table
and copies it to a DBF. Is this correct? What I was trying to do was
write a DBF table to the MDB table.
Murray
On Mon, 13 Jul 2009 14:37:59 +0200, "tom knauf" <hbg...@pdtgmbh.de>
wrote:
Dan Freeman
INSERT INTO TheView SELECT * FROM TheDBF
TABLEUPDATE()
Oversimplified, but it should get you going.
Dan
Murray
Thanks Dan,
I'll give it a try.
Murray
On Mon, 27 Jul 2009 16:02:20 -0700, "Dan Freeman" <sp...@microsoft.com>
wrote:
Murray
to INSERT all the records at one time (none of my attempts worked), I
tried it one record at a time with:
cSQLSTRING2 = "INSERT INTO Table1 " + cFields + " " + cData
gResult = SQLEXEC(nHandle, cSQLSTRING2)
It seemed to work fine... until a few days ago when I wrote a 225,593
record DBF table to MDB. I discovered that 3,158 records weren't
written and after writing the first 14 records, it inserted the next
15 records at the beginning of the MDB table. (It did the same with 9
records in a 2,000 record table.) I tried to discover if there was
any common element that was present in the records that were dropped
but couldn't find anything, but it consistently dropped the same
records. (Return code was -1 for those records.). I also couldn't
discover why it wrote the 15 or 9 records out of order.
So I went back to trying to write the whole file based on the above
code that worked for individual records:
cSQLSTRING3 = "INSERT INTO Table1 " + cFields + " SELECT ALL
FROM OutputData "
gResult2 = SQLEXEC(nHandle, cSQLSTRING3)
This didn't work. I tried a few variations with no success.
Any ideas?
Thanks,
Murray
On Mon, 27 Jul 2009 16:02:20 -0700, "Dan Freeman" <sp...@microsoft.com>
wrote:
>So once you've set up a remote view, make sure it's updateable and then
Stefan Wuebbe
> I thought I had this resolved. Instead of using a SELECT statement
> to INSERT all the records at one time (none of my attempts worked), I
> tried it one record at a time with:
>
> cSQLSTRING2 = "INSERT INTO Table1 " + cFields + " " + cData
>
> gResult = SQLEXEC(nHandle, cSQLSTRING2)
The SQL Insert syntax looks wrong to me, I'd suggest to try a
"parameterized" statement, something like:
cSQLSTRING2 = "INSERT INTO Table1 (" + cFields + ") Values(?m.cData)"
gResult = SQLEXEC(nHandle, cSQLSTRING2)
And then evaluate the returned value in m.gResult to see whether
there was an error, and if so, use AError() to get the reason.
hth
-Stefan
Murray
Actually this portion of my code did write records. The cData
contained the "Values(" and the closing paren. But the AERROR()
suggetion was very helpful. I wasn't aware of it that command. It
turned out that the records were being skipped if they contained
nulls.
So I'm working on getting rid of the nulls when the original MDB
file was imported into a DBF. That's what my project does. It
imports various types of files into DBFs, process the data and then
writes out the data in the format that the user wants. (Sounds
something like basic dataprocessing to me.) :)
I still haven't found out why it INSERTS records out of place but
I'm sure the solution will come up.
Also the INSERT INTO....SELECT... statement still doesn't work. The
AERROR() said that it was a syntax error in the INSERT INTO statement.
A friend said that he thought I might have to create an Access
application object to write the whole file at one time. Something
else I know nothing about. I'll probably have to try to hire someone
if I need that.
Murray
Murray
I've been reading the VFP Help & playing around with remote views.
Everything seems to point to their usefulness when retrieving data,
not writing data to it.
When using the SQLEXEC with the SELECT statement, the AERROR()
said:
"Connectivity error: [Microsoft][ODBC Microsoft Access Driver] The
Microsoft Jet database engine cannot find the input table or query
'OutputData'. Make sure it exists and that its name is spelled
correctly."
I believe that this is because it is looking for 'OutputData' on
the other side of the ODBC to select the data from.
Is this correct?
Murray
Dan Freeman
> Thanks for the reply Stefan.
>
> Actually this portion of my code did write records. The cData
> contained the "Values(" and the closing paren. But the AERROR()
> suggetion was very helpful. I wasn't aware of it that command. It
> turned out that the records were being skipped if they contained
> nulls.
>
> So I'm working on getting rid of the nulls when the original MDB
> file was imported into a DBF. That's what my project does. It
> imports various types of files into DBFs, process the data and then
> writes out the data in the format that the user wants. (Sounds
> something like basic dataprocessing to me.) :)
>
> I still haven't found out why it INSERTS records out of place but
> I'm sure the solution will come up.
>
> Also the INSERT INTO....SELECT... statement still doesn't work. The
> AERROR() said that it was a syntax error in the INSERT INTO statement.
This one?
> cSQLSTRING3 = "INSERT INTO Table1 " + cFields + " SELECT ALL
> FROM OutputData "
SELECT ALL isn't valid syntax. SELECT * instead.
Dan
Murray
I did change the ALL to * and got the same result.
Murray
On Mon, 15 Feb 2010 08:47:37 -0800, Dan Freeman <sp...@microsoft.com>
wrote:
Stefan Wuebbe
On 15.02.2010 17:22, Murray wrote:
> Also forgot to mention...
>
> I've been reading the VFP Help& playing around with remote views.
> Everything seems to point to their usefulness when retrieving data,
> not writing data to it.
RVs can do both: for being updatable, you'd need to have
a promary key field, and set the "SendUpdates" check box/
property,
>
> When using the SQLEXEC with the SELECT statement, the AERROR()
> said:
>
> "Connectivity error: [Microsoft][ODBC Microsoft Access Driver] The
> Microsoft Jet database engine cannot find the input table or query
> 'OutputData'. Make sure it exists and that its name is spelled
> correctly."
>
> I believe that this is because it is looking for 'OutputData' on
> the other side of the ODBC to select the data from.
>
> Is this correct?
Right, if 'OutputData' is a local VFP cursor, then Access
cannot "see" it.
So if you want to insert multiple rows, you can for instance
Scan/EndScan a Vfp alias and do an SqlExec(..., "Insert ..")
in each iteration, or use a Remote View or CursorAdpater
(buffer mode 5).
As for the MS Access automation option: last time I tried,
Vfp IntelliSense made it relatively easy IIRC; and I
believe you'll find many examples via web search for
keywords like "vfp createobject access.application"
hth
-Stefan
Murray
thought I'd post them as there might be others who encounter the same
problems.
1. Though I don't know why the records were written out of order in
the MDB, I made the one field (RECORDNUM) in the MDB table template
that I used to write the records to a primary key. Initially, as a
primary key, Access formatted it as a text field, so the records were
in the order 1,10,11,12,...100,101,..2,21,22,23, etc. Changing it to
NUMBER corrected that so the records are now in numerical order.
2. I got rid of the nulls, as data was being loaded into the VALUES
using NVL(), replacing the nulls with a space. I also tried IF
ISNULL() to see if it was any faster. Writing 225,000 records took
the same amount of time;3 min. 10 secs.
3. Initially, when I first started writing this procedure, no
records were being written. I discovered that the reason was that I
was enclosing the text values in double quotes. I replaced them with
single quotes which worked but created a new problem. If any of the
text data contained an apostrophe as in NAT'L or JOHN'S, the record
wouldn't write because it read the syntax as incorrect. Ultimately it
will require that I check each field when it is loaded from the
SCATTER array, replacing each apostrophe using STRTRAN. I anticipate
that it won't take that much time as it would if I elimiinated them
when the file is initially imported.
I hope this info is of some value to someone. Eventually I expect
to learn how to use a remote view and the SQL SELECT.
Murray