Database Question?

I've got a basic database question. I'm currently using Access as my database (a nightmare) and I want to keep the size of my database low. I want to link tables from one database to another. Then using only one database to reference as my datasource. I think this is possible. I remember doing it once on a localhost site. Now that i'm running my page on an intranet server I tried linking the tables and it does not work.

Can anyone explain the process to make this work?

Nakirema

Have you thought about using MSDE instead of Access? MSDE is free. You can't beat the price.

Access is definitely not ideal for the back end of a web site. I'd try to look into MySQL, or any of the commercial database servers.

linked tables in access on an intranet should be no problem

but if your database is greater than 500 megs you will have jet engine response time problems whether your databases are linked or not

i'm not sure about linking databases, but you can link tables simply by following the prompts after choosing File > Get External Data > Link Tables

No one has yet answered the question i'm asking. At the moment I must use Access. I'm currently looking into switching over to Microsoft SQL as my database. I do know how to link tables in Access.

The problem I'm having is that when i'm using Coldfusion to query tables in my database, i want to query table in another database as well. To do this I think I would need to link the tables then query the linked tables. However, I keep getting an error:

--------------------------------------------------------------------------------

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = S1000 (General error)


[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '\\is-18lg1\18muns_data$\Muns_Data.mdb'. It is already opened exclusively by another user, or you need permission to view its data.


SQL = "SELECT tbl_USERDATA1.* FROM tbl_USERDATA1 WHERE ((tbl_USERDATA1.USERNAME) = 'name');"

Data Source = "MUNS"


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (23:1) to (23:51) in the template file D:\InetPub\wwwroot\18muns\awc\user_log_page2.cfm.


Date/Time: 01/25/05 07:50:55
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
Remote Address: 132.15.187.8

--------------------------------------------------------------------------------

I hope this is alittle more understandable.

Nakirema

actually, for me that's worse, but that could just be me

any chance we could see the query? don't forget to indicate which ones are the linked tables

SQL = "SELECT tbl_USERDATA1.* FROM tbl_USERDATA1 WHERE ((tbl_USERDATA1.USERNAME) = 'name');"

Is the linked table..

The datasource for this query is Muns.mdb.

tbl_USERDATA1 is a table in Muns_Data.mdb.

Does this make sense?

Maybe my question is skewed.. This is a bad example for this question. If I have to databases and I must query something using information from them both.. Thats why I want to link the tables.

I came up with an idea that might work...

To query tbl_USERDATA1 using datasource Muns_Data.mdb.

Then to use this info I would query using datasource Muns.mdb and QofQ.

Ok i'm confussing myself...

Nakirema

i have an idea

instead of QofQ (last resort), try creating and saving a query in Muns.mdb that performs the retrieval from the linked table in Muns_Data.mdb

after saving the query, then run your CF query as

select * from mysavedquery

Thats a good idea... I should have thought of that... I'll be sure to come to you when I need help..

Nakirema

This will work with other database programs as well? I definately trashing access. Most likely I will switch to Microsoft SQL. I haven't used it yet so I don't know its capability.

I tried it and it does not work. Same ERROR

Nakirema

Not sure if anyone has answered this, but I have a similar issue.

I'm also forced into Coldfusion and Access. I have a database linking to a text file for simplifying end-user updating - i.e. all X-worker has to do is to rewrite the text file with a different text file that is sent from another database.

The problem is that Coldfusion is not seeing the table when I use the Application : Databases panel in Dreamweaver MX 2004. Is there any way to do this?

I don't use Dreamweaver (I hate it), so I won't be able to help on this one. If anyone knows, answer away. You might also try posting this on the Macromedia forums where you're more likely to find other Dreamweaver users.

well, i would not presume to know your network architecture, but i know i have had no success when it comes to linking to access dataabases that are not physically on the server. hmm.. on second thought, i have never had much success with access.

Thanks - actually, I moved the database and the txt file over to the server, in the same folder. It definitely was not properly linked so I relinked after it was on the server. I can open the database just fine in Access either on the server, or viewing from another computer. Dreamweaver sees the database, just not the table that is linked to the text file...just thought this was odd.

What do you use to code CFMX?? Studio,HomeSite or something else???