• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Create a connection to Access tables without using CF Administrator

Is there a way to create a connection to an access db with using the CF Administrator?  I want to read/update a table without creating the Data Source in CF Admin.  Thanks, sulzener
0
sulzener
Asked:
sulzener
  • 8
  • 6
1 Solution
 
_agx_Commented:
Yes, but only if you have to createObject("java", "...), do you? (Most shared hosts disable this feature).  

BUT ... Access should allow you access a table in an "external database".  Meaning you use an existing datasource to access another database.  I would suggest trying that approach first.  It is simpler and less error prone that the alternatives.  The syntax is something like this:

<cfquery name="getData" datasource="yourDatasourceName">
SELECT  *
FROM   SomeTableName IN 'c:\someOtherFolder\yourExternalDatabase.mdb'
</cfquery>

Link to a similar example (ie SELECT INTO .. rather than just a SELECT)
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_24267029.html
0
 
sulzenerAuthor Commented:
Thanks for your suggestion. I do not understand the createObject("java", "...) suggestion.  If you give me the complete syntax, I could try it  I'm trying to create a db connection to a CF site I do not have access to the Datasource, but I can upload cfm and mdb files.  Wondering if there might be a way to connect to the mdb tables.  Could you give me a few samples to try?
0
 
_agx_Commented:
Do you have _any_ Access sources setup already?  If you have at least one available

1) Upload the mdb database you want to read to your CF server, then
2) Try the query I posted above, substituting your datasource, table, and path to the mdb database.

I would really suggest trying that option first before resorting to the createObject("java"...) method.  (It is a bit more complicated)
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
sulzenerAuthor Commented:
No, I do not have ANY sources at all because I do not have access to the Admin.  It's a free CF hosting site.  I requested that one be setup for me, but I cannot seem to get a response.  So I thought I'd post this question and see if there might be a work-around since I can post the MDB file into the same place as my CFM.  
0
 
_agx_Commented:
Ah, okay.  If it is a free site, they may not give you access to createObject either.  Try this to quick test to verify it first.  You should see the text  "Yes, it works" or an error if you don't have access:

<cfset str = createObject("java", "java.lang.String").init("Yes, it works")>
<cfoutput>#str#</cfoutput>
0
 
sulzenerAuthor Commented:
Yes, it did work.
0
 
_agx_Commented:
Wow.  I am surprised.  Give me a minute to dig up the example.
0
 
_agx_Commented:
Btw, what version of CF is it - 7 or 8?

<cfdump var="#server#">
0
 
sulzenerAuthor Commented:
PRODUCTVERSION: 8,0,1,195765
0
 
_agx_Commented:
Here is a simple example of a select and update.  Just change the mdb path, table name, etc..

I was lazy, so the slightly modified source is entirely from this post.  
http://forums.adobe.com/thread/419345?tstart=0 
<cfset fullPathToMDBFile = "c:\myDatabases\test.mdb">
 
<!--- UPDATE example ---->
<cfset sqlStatement = "UPDATE SomeTable SET FirstName = 'Changed by Example 2' WHERE ID = 1">
<cfscript>
  classLoader = getClass().forName("com.inzoom.jdbcado.Driver");
  dm = createObject("java","java.sql.DriverManager");
  con = dm.getConnection("jdbc:izmado:Provider=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#fullPathToMDBFile#;Uid=;Pwd=;");
  st = con.createStatement();
  st.Execute( sqlStatement );
  st.close();
  con.close();
</cfscript>
 
<!--- SELECT example ---->
<cfset sqlStatement = "SELECT FirstName, LastName FROM SomeTable">
<cfscript>
  classLoader = getClass().forName("com.inzoom.jdbcado.Driver");
  dm = createObject("java","java.sql.DriverManager");
  con = dm.getConnection("jdbc:izmado:Provider=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#fullPathToMDBFile#;Uid=;Pwd=;");
  st = con.createStatement();
  rs = st.ExecuteQuery( sqlStatement );
  query = createObject("java", "coldfusion.sql.QueryTable").init(rs);
  st.close();
  con.close();
</cfscript>
 
<cfdump var="#query#">

Open in new window

0
 
sulzenerAuthor Commented:
That worked.  Thank you so much.  Is there any easy way to get the full path if I only know the access table name?  I was able to figure what it is now by doing this: <cfoutput>#cgi.path_translated#</cfoutput>

I then hard coded the fullPathToMDBFile as you did.  I am wondering if the path may change and then my site will stop working?  No big deal of this is not easy.  Thanks again so much.
0
 
_agx_Commented:
Since you know the name of the database, just use ExpandPath to get the absolute path:

<cfset fullPathToMDBFile = ExpandPath("/mysiteroot/myscripts/myDatabase.mdb")>

You could store the value in an application variable,  so you do not have to keep setting it.  Though I would recommend switching over to a datasource as soon as you get one.  The jdbc method is not nearly as simple as cfquery ;-)  
0
 
_agx_Commented:
... or from the current directory:

<cfset fullPathToMDBFile = ExpandPath("./nameOfYourDatabase.mdb")>
0
 
sulzenerAuthor Commented:
Greatly appreciate your answer and advise.  Thanks again!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now