ODBC connection problem

[spokex]

I have a need to programmaticly (sp?) copy the data in an Access database to a DBF.

I have looked at all the threads of ODBC connections but have had no success.

I can open the file manually with a view and then use 'copy to emplist' to save it as a table. This works fine, but I need it to happen each time a screen is loaded.

The Access file path is p:\spsi\client\2280.mdb.

Thanks,

Scott

 

[rgbean]

Scott,
If the view is defined in your Database (DBC), then you can just open it like a table (USE myView), do the copy (just like in the command window) and then close the view and use the new table.

Rick

 

[spokex]

Rick,
Thanks for you reply.

Every time I try to use the 'view' it opens a dialog box and I have to find the Access file manually.

I don't know how to prevent that from happening.

-Scott

 

[DSummZZZ]

Are you saying you want to create the ODBC connection on the fly?
If you already have the connection defined in the data sources or connection manager, you should be able to use code similar to this to query the data and throw it to a table:

h = SQLConnect('MyConnection', '')
STORE 0 TO nResult
nResult = SQLExec(h, 'SELECT * FROM recipes', 'MyCursor')
?nResult
DO CASE
   CASE nResult = -1
      ?'Error with connection'

   CASE nResult = 0
      ?'SQLExec still running'

   CASE nResult = 1
      ?'SQLExec completed'

   OTHERWISE
      ?nResult - 1
      ?? ' outstanding result sets'
ENDCASE

DO WHILE .T.
   IF SQLMoreResults(h) # 2
      WAIT WINDOW 'pause...' TIMEOUT 1
   ELSE
      EXIT
   ENDIF
ENDDO

SELECT MyCursor
COPY TO MyTable
?SQLDisconnect(h)

Dave S.

 

[spokex]

I must have something setup wrong. When I run this code I also get a dialog box that I have to manually find the Access Database.

Scott

 

[Mike Lewis]

Spokex,

Are you using the connection designer (that is, have you got a "connection object" in your DBC)?

If so, look to see what name is specified under "Connection source". Then go to your ODBC manager (in Control Panel) and look for that same connection. Click on Configure.

Now, in the resulting dialogue, can you see the correct name and path to the MDB file, just above the Select button? If not, click on that button, select the file, then OK out of all the dialogues.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[spokex]

Mike,
That was the problem. I had not put the path to the Access database in the ODBC setup.

Thanks,

Scott