pull data from access db

[waely]

hi all,
I have a page that displays information and images from database. it works fine on my website but i want to send this page as a newsletter. I can't refer to the database location as Server.MapPath or Source = C:\.. I have to specify the full path of my website and the loction of the dbase on the server in order for it to work, at least this is what I think needs. I tired to use:
Dim myConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; data source=

http://www.mysite.com/data/db.mdb)"

that gives me an error. can you please advise how to fix it or if there is another way it can be done with? thanks very much

 

[Isadore]

wae: try this:

Dim myConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath("fpdb\Sites.mdb;"))

..even tho it uses mapPath. For creating Excel files it works like a charm.

 

[waely]

Isadore,
I get the following error:
System.ArgumentException: Invalid path for MapPath '

http://mysite./data/db.mdb;'.

A virtual path is expected.

any ideas?
thanks

 

[PogoWolf]

for MapPath to work, you need to use a 'real' folder path, like 'C:\myDB\DB.mdb', you can't use a Virtual path that you have in your error string above.

Dim myConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath(".\Data\db.mdb;"))

Could also also Response.Write the mapPath in order to verify the path.

Hope that helps!

The PogoWolf

http://pogowolf.GamersCommune.com

 

[waely]

hi,
I don't need to use mappath. I want access my mdb from a different server. so the only way I can think of is set the data source = "

http://....mdb"

isn't that correct? or is there another way I can do it?

any help is appreciated.

 

[PogoWolf]

well one way would be to map a drive to the folder of the DB, and then use the mappath to it.

or if it's on a server, you could create a DNS to it.


The PogoWolf

http://pogowolf.GamersCommune.com

 

[waely]

I'm not sure how to do that. what I'm trying to do is this. emailing a newletter,which contains a data and images pulled from access database,to my email list.

any recommendations????

thanks

 

[Isadore]

wae: here's and SQL connection I made from one server to the another:

Dim sqlconn As SqlClient.SqlConnection
Dim cmdParr As SqlClient.SqlCommand
sqlconn = New SqlClient.SqlConnection("Server=appdata.xxx.yyy;uid=yourID;pwd=yourPassword;database=yourdatabase")

...where appdata.xxx.yyy is the URL to the server...

 

[waely]

Isador,
is it the same for access db connection with replacing teh SqlClient with oledb?

and do I have to include username and id to connect to access database?

thanks for your help

 

[Isadore]

wae: The above string was used successfully by me to connect from one database on the server (frontpage server) to another (SQL Server) - so 2 different databases in two different locations.

The trick was to inclued the dbase's URL and NAME, as you can see. Worked like a charm.

No, they are two entirely different connection strings.

 

[waely]

hi Isadore,
here what I have now:
-------------------------------
Dim strSQL As String
strSQL = "select * From concerts Where id = " & _ID & ""
Dim myConnection As String
= "Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("data/mydb.mdb")

Dim objConn As New OleDbConnection(myConnection)
Dim dataAdapter As New OleDbDataAdapter(strSQL, objConn)
Dim dataSet As New DataSet()
---------------------------------
can you please help by converting your sql conn to oledb connection?
thank you very much for any help.

 

[Isadore]

Sorry for the delay - was tied up last 24 hrs.

Well, here's how I'd write it:

Dim sqlCmd As OleDbCommand = New OLEDBCommand ("select * From concerts Where id=" & _ID)
Dim dbconn As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath("fpdb\TSSpww.mdb;"))
...

..so the command object and the connection string.

Question? Have you consider just reading the data out? Is an adapter and dataset necessary? Just curious.

 

[waely]

hi Isador,
I wanted to use access remote connection just like you indicated few comments back. I'm using a datarepeater in my codes to allign everything in a nice table.

thanks

 

[joxx]

On your webpage, the data that is pulled from the database is rendered as HTML.
Is it not just this that you want to email in your newsletter?
If so then you can use the mail object to mail the page.

 

[waely]

joxx,
I'm using a email campaign software for sending the newsletter.however, the newsletter has data that is pulled from access database so I'm trying to create a remote connection. it's the only way I can think of unless you recommend something else.

thanks

 

[link9]

The last time I checked, you could not connect to an Access database on a different server unless it was on the same network.

If it is on the same network, then you can use a UNC path:

\\machinename\sharename\path\databasename.mdb

But past that, you're going to be out of luck. It won't work for a machine that is not on your network. Only a product such as SQL Server, Oracle, MySQL, etc... are accessible over remote network connections.

-p

The answer to getting answered -- faq855-2992

 

[waely]

hmm, that sucks.... I would use MySql but the database that I want to connect to is already existed with hundreds of records. so I'm not sure what else I can do......

 

[Isadore]

Thanks Paul - that's right -- the SQL Server I was hitting and the frongpage server I was working off of (with Access) are on the same network here at the University.