ODBC, ADP, Or simply the VB Route.

Hello All,

I have an application running off an Access database. Im trying to convert the application to a server-client architecture and thus moving everything to SQL Server 2000.
I have read alot of articles about ways to accomplish that but I've still yet to decide on which approach is the best (best as in robust and scalable).

I pretty much eliminated the ODBC route due to all the layers of translation a request has to go through to reach SQL Server, although this route seems to be the quickes to accomplish.

Mind you I have plenty of time on hand and am willing to re-write the whole thing from scratch if it means a better app.

Now should I go the ADP route and keep Access as the user interface or should I rebuild the whole front End in pure Visual Basic that interacts with SQL Server? Im leaning towards the latter solution.

I haven't read any articles talking about rebuilding the whole app. using VB and SQL Server instead of just using ADP. Why so and which solution do you think is a better solution for a client-server architecture??

Thanks in advance for any replies to my questions.I don't quite understand your statement regarding "eliminating ODBC"
If you use RDO you'll be using ODBC, regardless whether it's a DSN-based or DSN-less connection object. How different it is vs using ADO? Capabilities are wider with the latter, but the "number of layers of translation" is actually is actually favoring the RDO solution.

As to ADP/Access...I'd drop it. Just print out the current set of forms and put it in a folder called "How not to write the front-end."

And when you start with your VB solution, make sure you architect the interface in such a way, where there is no need for bound controls, - that's what makes Access the most unfavorable tool.

ODBC Text file to SQL conversion

I have a large comma delimited text file (130K records) that I want to convert to an SQL data file. I want to use this data file as a datasource in a Visual Basic 2005 application.

Can I use the txt file directly?

If not, how can I convert/import the data into an SQL data file?

Thanks, Bob

Steve Kass has good info:
http://www.users.drew.edu/skass/sql/TextDriver.htm

You could also use openrowset() to query the data.

exec master..xp_cmdshell 'echo "this is a test" >> c:\temp\test.txt & echo
"this is a test2" >> c:\temp\test.txt'
go
select *
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=c:\temp\;HDR=NO',
'select * from test.txt')