SQL Commands and Access Data

[foxfire]

I want to copy data from an access table on a regular basis and not change the original Access mdb file. I use the following and I do get the data:

cConStr = "DSN=MS Access Database;DBQ=c:\Sally.mdb;DefaultDir=c:\;DriverID=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
nConMDB = SQLSTRINGCONNECT(cConStr)

SQLEXEC(1,'Select * from cells','tcDetails')
=SQLDISCONNECT(nConMDB)
SELECT tcDetails
GO TOP
BROWSE

I know nothing of SQL and got the above to work by using this site. The only problem is when I disconnect from the Access table, I get the error:

Invalid call issued while executing a SQLMORERESULTS() sequence.

Also, does my method look correct? My goal is to get data from Access without any changes to Access.
Any help would be appreciated.

 

[Mike Lewis]

Foxfire,

As far as I can see, your code is spot on. The reason it is failing is because you have somehow made the connection in batch mode, which means that the connection will stay open, waiting for you to request further cursors, which of course is not what you want.

I can't see how that came about, but I suspect it is something with the connection string. You could try removing the MaxBufferSize and PageTimeout entries in the string, to see if that solves the problem.

If that doesn't work, come back and we can take another look.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[craigsboyd]

Try changing your SQLExec() statement to the following...

SQLEXEC(nConMDB,'Select * from cells','tcDetails')

SweetPotato Software Website
My Blog

 

[foxfire]

With things mentioned in the above responses, I did a Google search and found some info at Microsoft that helped solve the issue. The Do/While fixed it. My modified code is:

cConStr = "DSN=MS Access Database;DBQ=c:\Sally.mdb;DefaultDir=c:\;DriverID=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
nConMDB = SQLSTRINGCONNECT(cConStr)
SQLEXEC(nConMDB,'Select * from cells','tcDetails')
DO WHILE SQLMORERESULTS(nConMDB)<>2
ENDDO
=SQLDISCONNECT(nConMDB)


SELECT tcDetails
GO TOP
BROWSE

Thanks for the help.

 

[Mike Lewis]

Foxcfire,

Craig is absolutely right. I missed the fact that you were passing 1 as the first param to SQLEXEC(), where you should have been passing the connection handle.

Once you've done that, I don't see that you should need to call SQLMORERESULTS(). Still, if it works, that's good.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[foxfire]

That's what I thought to. I did as Craig said, and I still recieved the same error. Once I put the Do/While in, all worked fine.

The link I got the info from is:

http://msdn.microsoft.com/library/d...us/dv_foxhelp/html/lngsqlmoreresultslp_rp.asp

 

[craigsboyd]

Strange that you should have to use that Do loop... I mean, you aren't doing a batch of select statements in your example code that you've posted, so it shouldn't be necessary (I've never run into this). I'm left wondering what the setting is in your DSN that is causing this behavior.

SweetPotato Software Website
My Blog

 

[Mike Lewis]

It does seem strange. On reflection, I can't see how substituting 1 for the connection handle should cause this problem .. Unless there was an existing connection open to Access in the same VFP session, and this happened to be connection number 1, and it was set to operate in batch mode.

I go back to what I said earlier. It must be something in the connection string that's causing it to operate in batch mode. FoxFire, I appreciate the problem appears to have gone away, but I feel sure there is something wrong still lurking in the code, and if I was you I would try to track it down and eliminate it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)