Connecting to both MS Access and SQL tables

I want to be able to update from either Excel Sheets or MS Access 2003 into a SQL 2005 database table programmatically. Is it possible to upload an Access table and then connect to it within ASP, along with a SQL table, and then query in the data wanted? If so - how would you copy up the Access DB into DiscountASP site or is that absolutely necessary? Thank you for your help.

 

...Yes you can connect to both and feed records from a page that updates.
This wouldn't be an easy example but I can help you with the web.config if that part is giving you trouble.
All the best,
Mark

 

Thank you wisemx. I appreciate the rapid turnaround.

Right now I connect with my SQL database with the following. I am replacing the credentials (UserID and Password) with generic.

Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.open = "Provider=sqloledb;Data Source=tcp:sql2k514.discountasp.net;Initial Catalog=SQL2005_428011_iysa;User ID=USER;Password=PASSWORD"

I also found on a forum that I can connect to an Access DB with the following:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\access2010iis7.accdb;Persis t Security Info=True

But also found these...
Dim strConnection As String = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + Server.MapPath("../_database/mydatabase.mdb")

or this in the config db...
<connectionStrings>
<add name="MyAccessDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<%=myAccessDBPath%>;Persist Security Info=False;"/>
</connectionStrings>

So, in answer I would appreciate some help in configuring this since it appears there is no set way to do so but many paths to the Buddha.

Also, what is the easiest way to upload an access db up to the server?

Thanks

 

I've done a webcast on connecting to Access in VS2010.
It makes doing this easy because even your Access DB in VS2010 can be a data server.

The conn string in the web.config looks like this:
<connectionStrings>
<clear/>
<add name="accdbConn" connectionString="Provider=Microsoft.ACE.OleDb.12.0;Data Source=|DataDirectory|access2010iis7.accdb" providerName="System.Data.OleDb"/>
</connectionStrings>

The easiest way to upload one I believe, if you are using VS, is with the Copy Website feature in VS.

 

I should have said earlier my versions:

SQL = 2005
VS = 2005
MS Access = 2003 but I could easily update.

Would it help to update other things? Like VS?

 

Also - that I'm carrying all of this out on a website using Classic ASP - basically asp and html. Some Javascript too. Thanks.

 

...Classic ASP I'd recommend Dreamweaver.
It even creates the Access and SQL Server page code for you, record sets and all.
Neither VS2010 or Expression Web do very good at classic ASP.
Of that group Expression Web v4 is the best but still not even close to Dreamweaver.

 

I don't imagine there's a programmer worth his/her salt that wouldn't recommend something different than what I'm using but I'm sticking with it at this time. So, I return to my question:

Can it be done with what I've given as my versions?
How would I easily copy up to the DiscountASP server an access db and where would I put it?

Thank you

 

...If you use ASP.NET the best folder for an Access DB is App_Data.
It's protected and automatically located via the folder structure.

With Classic ASP the best folder is _database, which is also protected but requires you to write all of the path structure via code.

This is the way I used to connect to an Access DB located in the _database folder:

<%
Dim MM_Conn2_STRING
MM_Conn2_STRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/_database/customerDetails.mdb") & ";"
%>