Read data from mdb and store in dbf

[ashnam10]

Hello,

I need help in reading mdb file and storing table to dbf
I am using following code but gives me connection error in some machines.

Need another approach
Thanks in advance
ps : If i open this myfile.mdb via double clicking it opens properly

My purpose is only to read from access table any urgent help is required. Instead of sqlstringconnect can i use any other approach?


temp_dir = "C:\temp"
m_fname = temp_dir + "\add1.dbf"
delete file &m_fname
m_string = []
m_drv = sys(5)
m_filename = m_drv + "\myfolder\myfile.mdb"
naccesshandle = sqlstringconnect( "DRIVER=Microsoft Access Driver (*.mdb, *.accdb); DBQ=&m_filename;Pwd=mypwd" )

if naccesshandle < 1
messagebox( "Invalid connection to DATA" )
return
endif

*/ Valid handle, now, query down the data to a local VFP cursor
nsqlans = sqlexec( naccesshandle, "select * from myfile", "C_CursorInVFP" )

if nsqlans < 1
messagebox( "Unable to get any data..." )
sqldisconnect( naccesshandle )
return
endif

*/ Done with connection
sqldisconnect( naccesshandle )

select c_cursorinvfp
copy to &m_fname
sele c
use &m_fname
brow

 

[Mike Lewis]

There's nothing wrong with your overall approach. It's pretty well the same as what I would do.

Given that the error only occurs on some machines, there must be something wrong with the machines on which it fails (rather than with your code). You say that it is a connection error. I assume you mean that you are seeing the "Invalid connection to DATA" message. If so, then the problem lies in your connection string.

So, check that you have the correct ODBC driver installed on the problem machines (Microsoft Access Driver (*.mdb, *.accdb)). Check also that the MDB file is present on the default directory, and that its path and filename is \myfolder\myfile.mdb. Finally, check that you are passing the correct password.

If none of that helps, keep looking for what's different on the machines where it fails compared to the ones where it succeeds.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

By the way, if your SQLSTRINGCONNECT() works, but the error is on the SQLEXEC(), then check that the target table is indeed named Myfile. The point is that Myfile is also the name of the database. The SELECT statement needs the table name, not the database name.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ashnam10]

Hello,

Thanks for your reply
My connection string fails. So what should i look for in the error machine? It has MS Access 2016 installed.
Any pointers others than ODBC driver? Since it already has Ms Access Installed.

Thanks in Advance

 

[JRB-Bldr]

You might want to look at:
Test remote SQL connectivity EASILY

You can separately check your ability to connect to each of the separate Access databases.
Once you have got BOTH of them working, you can then open the UDL files with notepad and you will see the connection string to that SQL you were testing with.

Once you have them defined you can use them in your SQLSTRINGCONNECT()

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

If you get the message "Invalid connection to DATA" because SqlStringConnect returned -1, then you get more info about why the connection failed with AERROR(laError) and inspecting the laError array (for example in the Locals window of the debugger). That's also true if SQLEXEC returns -1. AERROR will give you an array of information about an ODBC error, you'll find detail info on its help topic.

The MDB file could be missing or the ODBC driver. Notice, when you install VFP9 itself you don't get its ODBC driver installed (I'm at least 90% sure it was that way at VFP6 und 7 times, as when I needed it I had to install it separately), likewise even clients having an Access installation not necessarily have the Access ODBC driver installed, as Access database files work natively and self-contained without making an ODBC connection to themselves.

Bye, Olaf.

 

[TamarGranor]

Just want to point out the your use of the macro operator "&" could give you some trouble here. Never use it for paths, since they can include spaces. Use name expressions instead. For example, instead of:

[pre]delete file &m_fname[/pre]

use

[pre]delete file (m_fname)[/pre]

You can do this anywhere VFP expects a name and only a name.

Tamar