Urgent Database Advice and Assistance Needed !

[sjf]

Thanks for taking the time to read this.
I need to be able to pull records from several tables from an Access Database on a web site and place them into tables (that differ in structure) on a local Access Database.
First question is ... is this possibile?
Second question is ... where do I start looking for a process to achieve this?

Any advice and / or suggestions and / or code to do this (hoping a lot) would be much appeciated !
thanks
sjf

 

[link9]

Yes, it's possible. And best of all, easy --

By you asking that type of question, I wouldn't presume to be able to give you a lesson or anything, but can point you to some great resources and give you a little bit of concept and code on how this is done. Two great resources are

http://www.learnasp.com

(great tutorials -- just look under db and connections) and

http://www.devguru.com

(great downloadable html vbscript reference along with some tutorials to get you going).

Basically, connecting to a database boils down to two things -- a connection (think of this as the pipe through which your data will travel from the database to the client), and the recordset (think of this as a perfect replica of your database table, and it's what holds the data and the vehicle by which you access it).

You need to have some way of connecting to the database, and this is usually done with a DSN (Data Source Name), and greatly simplifies the process with ODBC (Open DataBase Connectivity). You set this up in the control panel of the server where your asp page will reside. You specify what database you want to connect to and give it an alias (the actual DSN -- usually something easy to remember like myDataBase or whatever)

That being said, you create a connection by saying:

dim con
set con = server.createobject ("ADODB.Connection")
con.open ("DSN=myDataBase")

There, you now have a connection to the database. Now, you have to actually get the data into a recordset:

dim rs
set rs = server.createobject ("ADODB.Recordset")
rs.activeconnection = con   'tell this recordset to use con
rs.open "SELECT * FROM tableName"

And there you are. You now have a replica of the data that exists in the table, tableName. You can refer to the different columns of data by the names that you have given them in the database table.

You can output it by saying:

response.write rs("columnName")

The above code will write to the browser whatever value is in the first row of the table under the column, columnName.

There are a plethora of functions that you use with the recordset, such as rs.movenext (to move to the next row), rs.movefirst, rs.movelast, etc...

I hope this gets you headed in the right direction, as posting much more here probably wouldn't be appropriate. Just visit the sites above, and do a little reading. It's a piece of cake once you get the hang of it.

good luck!
Paul Prewett

 

[sjf]

Paul,
Thanks for your response, unfortunately I think you have misunderstood my question.

I am easily able to connect to the WEB database and display that information on web pages using ASP script. I can also do the same on the LOCAL database using a local web server (ie Personal Web Server). However, what I have not been able to do is connect from the LOCAL database to the WEB database (they are two different databases with different structures) and pull information from the WEB database to the LOCAL database and visa versa.

If you have any advice with regards to that process it would be much appreciated.

 

[link9]

If all you are wanting to do is get the information back and forth, then I'm thinking you would want a table in either database that would correspond to the structure of whatever table you would want to pull from...

Then, you would get a recordset, and start trying to .addNew to the opposing table -- be sure to tell the script 'on error resume next', and to set your primary keys properly in order to control the data inserts.

That way, whatever unique records there were, would be inserted into the tables, and the duplicates would automatically be discarded... (and would produce no errors on insert because you told the script to ignore the errors)

Am I understanding your question now, or am I still completely off??

 

[sjf]

Closer, i understand the statements required to insert the data etc ... thanks for the tip on the On Error Resume Next, that will be useful.

What i do not understand is how to actually connect to the web database from the local database.... what do I actually do to create the connection on a local machine in Access and grab data from a remote database stored on a web server.
cheer
simon

 

[link9]

I may very well need to be corrected here (and I'm sure I will be if so), but I don't think you can create a direct connection between the two Access databases...

I think you will need to create two recordsets with the data that you want to examine, update the two recordsets, and then run updates on the respective databases from those two recordsets.

If you provided a specific example of exactly what it is you are trying to accomplish (possibly some code, or some column names and sample data), then maybe we can get a little further in this discussion...