FoxPro and Access

[murray1840 via AccessMonster.com]

I am trying to take a foxpro 7 table and copy it to access to make a access
mdb. I have included the code I am using. The code posted works as far as
making my MDB and new table and so far it copys some records to the new
access table. I have 27 fields I need to get copied but I cant seem to get
passed 11 fields I keep getting a " Command contains unrecognized phase\
keyword" error. Any help would be great.
Murray

*-- connect to Access
lcConnectionStr = "DRIVER={Microsoft Access Driver (*.mdb)};" +;
"Dbq=c:\foso\foso.mdb;" +;
"Uid=Admin;" +;
"Pwd=;"
lnAccess = SQLSTRINGCONNECT(lcConnectionStr)

*-- open VFP table to export
USE "C:\foso\form1.DBF"

*-- optionally create the table in Access
SQLEXEC(lnAccess, "CREATE TABLE form2 (uic varchar(5), calendr_yr varchar(2),
serial_no varchar(3), srvc_code varchar(1), form_type varchar(8),priority
varchar(1),activity varchar(40),address varchar(120),poc varchar(25),
dsn_number varchar(15),com_number varchar(15))")

*-- create SPT cursor
?SQLEXEC(lnAccess, "SELECT * FROM form2", "form2")
*-- make SPT cursor updatable
CURSORSETPROP("Buffering", 5)
CURSORSETPROP("Tables", "form2")
CURSORSETPROP("UpdatableFieldList", "uic,calendr_yr,serial_no,srvc_code,
form_type,priority,activity,address,poc,dsn_number,com_number")
CURSORSETPROP("UpdateNameList", 'uic form2."uic", calendr_yr form2.
"calendr_yr",serial_no form2."serial_no",srvc_code form2."srvc_code",
form_type form2."form_type", priority form2."priority",activity form2.
"activity", address form2."address", poc form2."poc",dsn_number form2.
"dsn_number",com_number form2."com_number'")
CURSORSETPROP("SendUpdates", .t.)
*-- append data from VFP table. Note that at this stage you can do anything
APPEND FROM DBF("form1")
*-- committ changes to Access
?TABLEUPDATE(.t.)

 

[Guest]

Why not just export the data from FoxPro in some format like CSV or Excel
that can be readily imported into Access? You might even be able to link the
FoxPro table from Access and extra the data that way.

 

[murray1840 via AccessMonster.com]

If I export to excel then I would need to export to access, right?

Why not just export the data from FoxPro in some format like CSV or Excel
that can be readily imported into Access? You might even be able to link the
FoxPro table from Access and extra the data that way.

I am trying to take a foxpro 7 table and copy it to access to make a access
mdb. I have included the code I am using. The code posted works as far as

[quoted text clipped - 37 lines]

*-- committ changes to Access
?TABLEUPDATE(.t.)

 

[Cindy Winegarden]

Hi Murray,

Have you tried creating them MDB with an empty table and creating a remote
view to the table in VFP? Then all you need to do is APPEND the data to the
view. Post back if you don't know how to do this.

 

[Cindy Winegarden]

Silly me! That's essentially what you did.

You haven't said which line of code you get the error on. FoxPro has a limit
on the length of a quoted string. Try building your SQL String like:

SQLEXEC(lnAccess, ;
"CREATE TABLE form2 (uic varchar(5), calendr_yr varchar(2), " + ;
"serial_no varchar(3), srvc_code varchar(1), form_type varchar(8), " + ;
"priority varchar(1),activity varchar(40),address varchar(120), " + ;
"poc varchar(25), dsn_number varchar(15),com_number varchar(15))")

Alternatively, try the Create Table statement with a few fields at a time to
try to narrow down to a bad field.

If you can zip up and send me (address below) a small amount of data and the
code you are using I'll be happy to play with it to see if I can find
anything that's causing the problem.

 

[Guest]

Yes. Well to be precise you'd need to import into Access. Therefore it would
be a two-step process. Foxpro to Excel or CSV. Then Excel or CSV to Access.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

If I export to excel then I would need to export to access, right?

Why not just export the data from FoxPro in some format like CSV or Excel
that can be readily imported into Access? You might even be able to link the
FoxPro table from Access and extra the data that way.

I am trying to take a foxpro 7 table and copy it to access to make a access
mdb. I have included the code I am using. The code posted works as far as

[quoted text clipped - 37 lines]

*-- committ changes to Access
?TABLEUPDATE(.t.)

 

[Cindy Winegarden]

I got the table to play with. The following code worked as expected for me:

Set Multilocks On
lcConnectionStr = "DRIVER={Microsoft Access Driver (*.mdb)};" +;
"Dbq=D:\Temp\foso.mdb;" +;
"Uid=Admin;" +;
"Pwd=;"
lnAccess = SQLSTRINGCONNECT(lcConnectionStr)
SQLEXEC(lnAccess,
"CREATE TABLE form2 (uic varchar(5), calendr_yr varchar(2)," + ;
"serial_no varchar(3), srvc_code varchar(1), form_type varchar(8)," + ;
"priority varchar(1),activity varchar(40),address varchar(120)," + ;
poc varchar(25), dsn_number varchar(15),com_number varchar(15))")
? SQLEXEC(lnAccess, "SELECT * FROM form2", "form2")

CURSORSETPROP("Buffering", 5)
CURSORSETPROP("Tables", "form2")
CURSORSETPROP("UpdatableFieldList", ;
"uic,calendr_yr,serial_no,srvc_code,form_type," + ;
"priority,activity,address,poc,dsn_number,com_number")
CURSORSETPROP("KeyFieldList", "serial_no")
CURSORSETPROP("UpdateNameList", ;
"uic form2.uic, calendr_yr form2.calendr_yr,serial_no form2.serial_no,"
+ ;
"srvc_code form2.srvc_code,form_type form2.form_type, priority
form2.priority," + ;
"activity form2.activity, address form2.address, poc form2.poc," + ;
"dsn_number form2.dsn_number,com_number form2.com_number")
CURSORSETPROP("SendUpdates", .T.)

Unfortunately your problem is that your original code can't get past 15
fields and yet it only lists 11 fields to create in the Access table and the
same 11 fields in the Fox code to update it. I don't have your original code
that creates an Access table with all the fields that were in the table you
sent.

While your original Create Table statement doesn't hit the FoxPro
250-character limit on a quoted string I'm thinking that the Create Table
statement for all 31 fields in the data you sent actually passes the 250
character limit. If you break up the Create table string the way I have
above, and also break up the UpdatableFieldList and UpdateNameList you
shouldn't have any problems working with all 31 fields.

 

[murray1840 via AccessMonster.com]

Cindy,

Thanks sooo much for taking the time to look at this, I to found out about
the 255 character limit on pass-thru SQL via VFP, I wrote several
UpdateNameList calls each sending less then 255 and it does work.

Thanks again

Murray

I got the table to play with. The following code worked as expected for me:

Set Multilocks On
lcConnectionStr = "DRIVER={Microsoft Access Driver (*.mdb)};" +;
"Dbq=D:\Temp\foso.mdb;" +;
"Uid=Admin;" +;
"Pwd=;"
lnAccess = SQLSTRINGCONNECT(lcConnectionStr)
SQLEXEC(lnAccess,
"CREATE TABLE form2 (uic varchar(5), calendr_yr varchar(2)," + ;
"serial_no varchar(3), srvc_code varchar(1), form_type varchar(8)," + ;
"priority varchar(1),activity varchar(40),address varchar(120)," + ;
poc varchar(25), dsn_number varchar(15),com_number varchar(15))")
? SQLEXEC(lnAccess, "SELECT * FROM form2", "form2")

CURSORSETPROP("Buffering", 5)
CURSORSETPROP("Tables", "form2")
CURSORSETPROP("UpdatableFieldList", ;
"uic,calendr_yr,serial_no,srvc_code,form_type," + ;
"priority,activity,address,poc,dsn_number,com_number")
CURSORSETPROP("KeyFieldList", "serial_no")
CURSORSETPROP("UpdateNameList", ;
"uic form2.uic, calendr_yr form2.calendr_yr,serial_no form2.serial_no,"
+ ;
"srvc_code form2.srvc_code,form_type form2.form_type, priority
form2.priority," + ;
"activity form2.activity, address form2.address, poc form2.poc," + ;
"dsn_number form2.dsn_number,com_number form2.com_number")
CURSORSETPROP("SendUpdates", .T.)

Unfortunately your problem is that your original code can't get past 15
fields and yet it only lists 11 fields to create in the Access table and the
same 11 fields in the Fox code to update it. I don't have your original code
that creates an Access table with all the fields that were in the table you
sent.

While your original Create Table statement doesn't hit the FoxPro
250-character limit on a quoted string I'm thinking that the Create Table
statement for all 31 fields in the data you sent actually passes the 250
character limit. If you break up the Create table string the way I have
above, and also break up the UpdatableFieldList and UpdateNameList you
shouldn't have any problems working with all 31 fields.

If you can zip up and send me (address below) a small amount of data and
the code you are using I'll be happy to play with it

passed 11 fields I keep getting a " Command contains unrecognized phase\
keyword" error. Any help would be great. [quoted text clipped - 6 lines]
varchar(1),activity varchar(40),address varchar(120),poc varchar(25),
dsn_number varchar(15),com_number varchar(15))")

 

[Cindy Winegarden]

Please understand that the limit is on a quoted string - i.e. something
between quotation marks. Breaking up a long string and concatenating the
pieces together works just fine:

*-- Doesn't work
lcSomething = "750 Chars Here"
*-- Works
lcSomething = "250 Chars Here " + "250 Chars Here " + "250 Chars Here "

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
(e-mail address removed)

Cindy,

Thanks sooo much for taking the time to look at this, I to found out about
the 255 character limit on pass-thru SQL via VFP, I wrote several
UpdateNameList calls each sending less then 255 and it does work.

Thanks again

Murray

I got the table to play with. The following code worked as expected for
me:

Set Multilocks On
lcConnectionStr = "DRIVER={Microsoft Access Driver (*.mdb)};" +;
"Dbq=D:\Temp\foso.mdb;" +;
"Uid=Admin;" +;
"Pwd=;"
lnAccess = SQLSTRINGCONNECT(lcConnectionStr)
SQLEXEC(lnAccess,
"CREATE TABLE form2 (uic varchar(5), calendr_yr varchar(2)," + ;
"serial_no varchar(3), srvc_code varchar(1), form_type varchar(8)," +
;
"priority varchar(1),activity varchar(40),address varchar(120)," + ;
poc varchar(25), dsn_number varchar(15),com_number varchar(15))")
? SQLEXEC(lnAccess, "SELECT * FROM form2", "form2")

CURSORSETPROP("Buffering", 5)
CURSORSETPROP("Tables", "form2")
CURSORSETPROP("UpdatableFieldList", ;
"uic,calendr_yr,serial_no,srvc_code,form_type," + ;
"priority,activity,address,poc,dsn_number,com_number")
CURSORSETPROP("KeyFieldList", "serial_no")
CURSORSETPROP("UpdateNameList", ;
"uic form2.uic, calendr_yr form2.calendr_yr,serial_no
form2.serial_no,"
+ ;
"srvc_code form2.srvc_code,form_type form2.form_type, priority
form2.priority," + ;
"activity form2.activity, address form2.address, poc form2.poc," + ;
"dsn_number form2.dsn_number,com_number form2.com_number")
CURSORSETPROP("SendUpdates", .T.)

Unfortunately your problem is that your original code can't get past 15
fields and yet it only lists 11 fields to create in the Access table and
the
same 11 fields in the Fox code to update it. I don't have your original
code
that creates an Access table with all the fields that were in the table
you
sent.

While your original Create Table statement doesn't hit the FoxPro
250-character limit on a quoted string I'm thinking that the Create Table
statement for all 31 fields in the data you sent actually passes the 250
character limit. If you break up the Create table string the way I have
above, and also break up the UpdatableFieldList and UpdateNameList you
shouldn't have any problems working with all 31 fields.

If you can zip up and send me (address below) a small amount of data and
the code you are using I'll be happy to play with it

passed 11 fields I keep getting a " Command contains unrecognized
phase\
keyword" error. Any help would be great.

[quoted text clipped - 6 lines]

varchar(1),activity varchar(40),address varchar(120),poc varchar(25),
dsn_number varchar(15),com_number varchar(15))")