|
| |
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
 |
1 Access database to another - 5/5/2005 15:30:03
Hi all, I have two access databases on two separate URL's. They are both on the same server though, and can have permissions set to write from one to the other (according to my host, who actually hosts Outfront).
1. Is it possible to create an insert into that will go from one database to the other...and.,.....
2. Both databases have different field names...can i insert fieldname "Name" in the first database into fieldname "Custname" in the second database?
I think i can, but before i go putzing around, i just wanted to know if this is all possible.
Oh, and right now they are in fpdb folders, i'll be creating dns connections through the host for each, and transfering them from the folders to the host connections.
Thanks in advance.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: 1 Access database to another - 5/6/2005 9:28:27
I don't think you'll be able to do it with the DRW but using straight ASP and having the correct read/write priv's, I suspect it's possible.
Never done it but the theory seems correct:
1. Open first DB and grab all the records you want to use and stuff 'em into an array. Close DB.
2. Open second DB and loop thru array with INSERT statement. Close DB.
Have you started at all?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: 1 Access database to another - 5/6/2005 15:53:45
Hi rdouglas...ok here' s the deal...so far.
-I called the host, said i wanted user read/write permissions added for both sites. So they can each use the data on the other site.
-I created a DNS with the database in one site.
-In frontpage i created a data connection using an outside data source.
-Created a page called test.asp
-Created a simple data area using that database and i now can access all the data on one site through the other...this makes me think i'll be able to create an insert into that will update a table in site "a" from site "b" and visa versa....
mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: 1 Access database to another - 5/10/2005 10:19:10
Rdouglas, or anyone else out there.
Im now trying to send the info from one record to the second database located on another site. They unfortunately have different fieldnames, but...here's the problem...I get an error that says cannot find table ceorders (which is the database im trying to send to...but have proper privs set.).
What rdouglas wrote above is, i have to open the first database and put it into an array, then close it and then open the second database and insert the data.
Im not familiar with arrays. I need to know how to open one database, and put the data (for one record with only specific fields) into an array, how to close that one, and open the other, and insert the data into database 2. Here's my insert that i have now, and realize why it didnt work....database 2 isnt opened..so how do i do this? Or can someone point me someplace where i can find out....
I appreciate it...here's the insert code (that doesnt work of course, but gives fieldnames).
db1=contacts.mdb and db2=ceorder.mdb
INSERT INTO ceorder (Cont_Last_Name, Acct_Name, Acct_Street_Address_1, Acct_Street_Address_2, Acct_City, Acct_State, Acct_Zip, Acct_Main_Phone, lastupdateby, up) VALUES ('::toname::' , '::tocompany::' , '::toaddress::' , '::toaddress2::' , '::tocity::' , '::tostate::' , '::tozip::' , '::tophone::' , '::repchanges::' , '::upn::')
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: 1 Access database to another - 5/10/2005 11:42:50
1. Are they both Access DB's?
2. Are they both on the same 'box'?
3. What are the phsical paths to the databases: For instance, D:\Inetpub\wwwroot\web1\fpdb\mydatabase.mdb
4. What are the two SQL statements you have? One would probably be a SELECT and the other one would probably be an INSERT.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: 1 Access database to another - 5/10/2005 11:54:18
1. Both Access
2. Both on same box
3. physical path is as you indicated (web1 , web2)
4. I have no sql statement yet for either...just the insert i gave before..that i know is incorrect.
Thanks
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
dzirkelb1
Posts: 1321
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: 1 Access database to another - 5/10/2005 12:09:46
The general way to place the data into an array is as follors:
<%
DIM connTemp
DIM sqlTemp
DIM rsTemp
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/myDatabase.mdb")
set connTemp=server.createobject("adodb.connection")
connTemp.open myDSN
sqlTemp = "sqlstatement"
set rsTemp = connTemp.Execute(sqlTemp)
IF rsTemp.eof THEN
Response.write("There was a problem with "&sqlTemp&")
ELSE
myArray=rsTemp.getrows
END IF
connTemp.close
rsTemp.close
set connTemp=nothing
set rsTemp=nothing
%>
That puts the data into an array which then can be called by myArray(c,r) where C is the column, R is the row.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: 1 Access database to another - 5/10/2005 12:39:55
Can you post the physical path in question 3 above? Please be very specific.
Also need specific answers to 4 like:
What is each database name?
What is each table named.
What specific fields match to what specific fields (from 1 table to the second)
Again please be specific.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: 1 Access database to another - 5/10/2005 15:05:21
I sent you a PM, but i received an error. I'd prefer not to put this info here if you dont mind....you can send me an email at mfalk@websitedrive, or you can pm me for the info if you prefer...but id really appreciate the help and lesson.
mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: 1 Access database to another - 5/13/2005 9:50:12
Just for info, this is the solution we came up with using generic field and table names:
<%
FUNCTION stripQuote(textIn)
stripQuote = Replace(textIn,"'","''")
END FUNCTION
DIM myDSN1, myDSN2, mySQL, myTempArray
myDSN1 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=D:\InetPub\wwwroot\web1\fpdb\DB1.mdb"
myDSN2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=D:\InetPub\wwwroot\web2\fpdb\DB2.mdb"
mySQL = "SELECT field1table1,field2table1,field3table1 FROM tableInDB1"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN1
set rstemp=conntemp.execute(mySQL)
IF rstemp.eof THEN
response.write "No records inDB1 that match<br>"
Call CloseAll
ELSE
myTempArray=rstemp.getrows
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN2
FOR i = 0 TO ubound(myTempArray,2)
mySQL = ""
mySQL = mySQL & "INSERT INTO tableInDB2 (field1table2,field2table2,field3table3) Values ("
FOR j = 0 TO ubound(myTempArray,1)
mySQL = mySQL & "'" & stripQuote(myTempArray(j,i)) & "',"
NEXT
mySQL = left(mySQL,len(mySQL)-1) & ")"
set rstemp=conntemp.execute(mySQL)
NEXT
Call CloseAll
END IF
SUB CloseAll
conntemp.close
set conntemp=nothing
END SUB
%>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: 1 Access database to another - 5/13/2005 10:11:13
So everyone knows. Rdouglas is a genius! lol.
The code above that Rdouglas posted is currently being used for the following..in case anyone wants some kind of reference to how it can be used and what for.
I currently have multiple databases on multiple sites. I needed a solution that would let me send data in our contact manager to these different databases, some for sales, some for invoicing, some for membership to our association.
1. Send contact info to create an invoice in a separate database from our contact manager.
2. Create New sale from contact manager to one of our customer service sites.
3. create new members in member listing through contact manager and also create a login in spooky login for that new member.
The obvious would have been to have sites under one roof, but, we couldnt do that, and it was a matter of coming up with an idea that would enable us to use what we have, more efficiently. Rdouglas came up with that solution.
**Hint, make sure you have proper permissions set to allow your databases or sites to read/write to one another. If you don't know how...contact your host and they can set these permissions / privelages for you.
Hope this helps.
Mark
_____________________________
Anyone that says, "All you have to do is.."...Please leave the planet.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
