Synching VFP60 tables with Access file 1

[foxrainer]

My current program has 3 main tables which need to be synched, via Access, to a Pocket PC. The data is being used by a program written with eVB ( I don't know of any other way to program with the PPC!).

My question: How can I establish a remote connection with an Access Table which can be updated, from VFP, with current information, hopefully as a table based update when the VFP program is exited?

Thanks in advance for any help

 

[darrellblackhawk]

Lookup the documentation on remote views.

You can set up a system or file DSN on the system where
the updates will occur from.

Come back after reading a little more.

Here's a short snippet showing how to make a simple
sql connection to an Access DB and make some minor updates:

Darrell

[tt]
SUSP

* Connect to a remote DB.
* I set up this in my system DSN's via ODBC
* I used the default samples folder in Office10

gnConnHandle = SQLSTRINGCONNECT('dsn=NorthWind;uid=;pwd=')

* Make sure you get a valid connection handle

IF gnConnHandle < 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE


* Shows how to get tables and views on a remote DB

nResult = SQLTABLES(gnConnHandle, &quot;'VIEW', 'SYSTEM TABLE'&quot;, &quot;tblsNviews&quot


* Execute a sql statement on the remote table

nResult = sqlexec(gnConnHandle,&quot;select CustomerID, CompanyName from Customers order by CustomerID&quot;,&quot;resultset1&quot


* Browse the original data in the result set

BROW


* Change the company name of one of the customers

nResult = sqlexec(gnConnHandle,&quot;update Customers set CompanyName = CompanyName+'a' where CustomerId='ALFKI'&quot


* Get the result set again

nResult = sqlexec(gnConnHandle,&quot;select CustomerID, CompanyName from Customers order by CustomerID&quot;,&quot;resultset2&quot


BROW && And browse it again to verify the change


* Change it back
* ( a little overkill vis-a-vis len/ltrim/etc., since
* Access or the ODBC driver will trim it anyway as far
* as I can tell )
nResult = sqlexec(gnConnHandle,&quot;update Customers set CompanyName = left(CompanyName,len(ltrim(CompanyName))-1) where CustomerId='ALFKI'&quot


* And verify it changed back

nResult = sqlexec(gnConnHandle,&quot;select CustomerID, CompanyName from Customers order by CustomerID&quot;,&quot;resultset3&quot
BROW

* Disconnect!
SQLDISCONNECT(gnConnHandle)

ENDIF
[/tt]

 

[foxrainer]

Darrell,

thank you very much for the helpful post.

My dilemma is not making the connection, I do have a connection. My question is: How do I make changes to A FVP table, and have the changes reflected in the Access table?

I do need the VFP tables to be tables, not views (otherwise I could just use a remote view).

Any help would again be appreciated,

Rainer

 

[darrellblackhawk]

A little more on sql syncing to Access.

The code snippet below highlights the basis for
creating DSN-Less connections to a remote Database.

There are a number of parameters that can be
used in the connection string, but these are the
bare minimum.

As far as updating the data in the Access table,
you'll need to become conversant with SQL syntax
if you are not already. You'll of course need to
know the structure of the Access tables, relations,
etc.

Make a duplicate of the Access database and begin
playing with it. You can use the snippet below as
a basis to begin exploring the structure of the tables.

Also, you'll more than likely have to deal with some
datatype conversion issues, but if not, consider yourself
lucky.

Hope this helps a little.

Darrell


[tt]
LOCAL cConnectString, nConnHandle, nResult

cConnectString=;
&quot;Driver={Microsoft Access Driver (*.mdb)};&quot;+;
&quot;DefaultDir=\\MyServer\company\Northwind;&quot;+;
&quot;DBQ=Northwind.mdb;&quot;+;
&quot;UID=;&quot;+;
&quot;PWD=;&quot;

nConnHandle = SQLSTRINGCONNECT(cConnectString )

* Make sure you get a valid connection handle

IF nConnHandle < 0
MESSAGEBOX( ;
'Cannot make connection', 16, 'SQL Connect Error' ;
)

ELSE

* Get table, view, and system table names
nResult = 0

nResult = ;
iif(SQLTABLES(nConnHandle, &quot;'TABLE'&quot;, &quot;tbls&quot==1, ;
bitor(nResult,1),nResult)

nResult = ;
iif(SQLTABLES(nConnHandle, &quot;'VIEW'&quot;, &quot;views&quot==1, ;
bitor(nResult,2),nResult)

nResult = ;
iif(SQLTABLES(nConnHandle, &quot;'SYSTEM TABLE'&quot;, &quot;systbls&quot==1, ;
bitor(nResult,4),nResult)

*** Do some processing ***
* ...
* ...
* ...

* Disconnect!
SQLDISCONNECT(nConnHandle)
ENDIF
[/tt]

 

[foxrainer]

Darrell,

thank you very much,

I think this gives me the basis I can use to start researching!

Rainer