Solution to my Initial problem...
Ive worked out that the connection string with the provider only accepts the Dos 8.3 file naming convention...
I can now connect and retrieve data from the DB using the connection string,
connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=j:\" & ";Extended Properties=dBase III;"
and renaming the datbase from NEW_DATABASE.DBF to NEW_DATA.DBF, this has allowed me to apply the following sql statement.
Reworked SQL statement..., change point in bold with file name changed also.
sql = "Select * FROM NEW_DATA"
This now allows the dataset to be filled, previously I had the "Microsoft Jet database engine could not find the object"
Hopefully this information assists someone else in a similar situation...
My next question would be ofcourse..
How do i get around the 8.3 file format restriction, based on the assumption that I cannot rename the original file, I can ofcourse copy the file to a file name convention that suites, however I prefer to avoid this work around if possible.
I expect that there may be an alternative option, maybe to use a different driver to connect to the .DBF file so that this 8.3 file format issue would be negated.
Based on that last assumption can anyone please advise me of a different driver setup to access the .DBF .MDX files???
OR Alternatively all ideas are welcome, in my opinion its best to ask the expert and draw from others experience when possible..
Any advice is appreciated, please assist if possible..