Question Send/Receive Data from Access Database over the network

[firas489]

Hello,

I was wondering if there is a possible way to Send/Receive Data from a MS Access Database over the network without having a drive or folder sharing activated?

A friend once suggested using SOAP to do so,
i like the idea in a way that when the data is received, its stored in a DataSet without extra formatting or anything,

Is there a way to do so without using SOAP...??
Or in other words, can i do that in the same way or idea of connecting and exchanging data with SQL Server remotely through a network port?



Thanks,

Best Regards
Firas S Assaad

 

[cjard]

You'll have to write your own "server" to receive the soap, query the db and send it back. I take it you cant just use SQLS?

Btw, Access is pretty clever in some network scnearios. for some queries it will contact the jet drivers on the machine where the db is hosted and ask them to run the query, sending on the results, rather than drag the entire data over the network.

 

[firas489]

You'll have to write your own "server" to receive the soap, query the db and send it back. I take it you cant just use SQLS?

thats what i thought, but do you recommend using SOAP protocol? or is there an alternative method?

And yes, unfortunately, im forced and stuck with Access...

Btw, Access is pretty clever in some network scenarios. for some queries it will contact the jet drivers on the machine where the db is hosted and ask them to run the query, sending on the results, rather than drag the entire data over the network.

Exactly,
but the question is, how do i send the data to the Client (on a server-client application scenario)...
I can send the results as a string and play with them on the client-side
but i was hoping to find a way where i can simply stick the received data from the server into a dataSet object, where on the client side i will be able to deal with the retrieved data as if they where coming straight from the Database...

Hope that made sense!


Thanks anyhow,


Best Regards
Firas S Assaad

 

[cjard]

dude, just dont do it. Jet can be upsized to SQLServer by a wizard, and SQLServer is a proper server based DB

The amount of time you will spend messing about writing a "jet db server" will cost far more than one SQLServer licence.

I remember your other thread about parsing dates where you were "stuck with" some old technology there too.. Seriously, if you came in here asking "I've got 486 DX2/66 running windows 3.11, how can I write some code to shoe the .NET framework into working on it" you'd be told to upgrade. There comes a point where you *have* to dump the old stuff and get on with something new

If you have another app that connects only to the access db, written in some ancient language and the source code is dead and gone, then consider rewriting that too.



I've just thought.. One route you could look at is running a sqlserver instance and attaching an Access DB to it via OLE/ODBC - i dont know if it is updatable but you can try.. And if your ancient app connects using DSNs then you can try making a DSN that points to SQLS instead of access, then upsize, and see can you get the old app working with a new database.. but youre creating future headaches

Good luck

 

[firas489]

dude, just dont do it. Jet can be upsized to SQLServer by a wizard, and SQLServer is a proper server based DB

The amount of time you will spend messing about writing a "jet db server" will cost far more than one SQLServer licence.

I remember your other thread about parsing dates where you were "stuck with" some old technology there too.. Seriously, if you came in here asking "I've got 486 DX2/66 running windows 3.11, how can I write some code to shoe the .NET framework into working on it" you'd be told to upgrade. There comes a point where you *have* to dump the old stuff and get on with something new

If you have another app that connects only to the access db, written in some ancient language and the source code is dead and gone, then consider rewriting that too.

: Yes you are right my friend,
I should move on...
let me give you a quick idea of what I'm dealing with...
my work is based on an old program that is based on an Access Database (BTW, ver 97 :)
And in the same time I'm developing programs and tools/utilities that are based on new technology (.NET and SQL Server)
And many times, i suffer from building bridges between the two technologies.
This old program is not mine and never was mine, nor i can re-write it or do my own. Its something that I'm basically stuck with for the rest of my life (unless the owners decide to move on)

I've just thought.. One route you could look at is running a sqlserver instance and attaching an Access DB to it via OLE/ODBC - i dont know if it is updatable but you can try.. And if your ancient app connects using DSNs then you can try making a DSN that points to SQLS instead of access, then upsize, and see can you get the old app working with a new database.. but youre creating future headaches

Good luck

Thank you very much for this thought, it really helped a lot.
I had found a way to do it,
its through Linked Servers in SQL Server (BTW, I'm using SQL 2005 Express)...
I connect the Access Database with the SQL Server and make it accessible throughout the network...
I was successfully able to run SELECT and UPDATE Statements.
I will go on more testings with this method, and hopefully it would be stable and reliable...

Once again, thank you very much for your help,
and trust me, once they leave access and move to newer technologies, i will be the first to adopt!


Best Regards
Firas S Assaad