Using SQL with MS Access 1

mcklsn · May 29, 2003

I am querying an MS Access database from Java. I want to find out if a certain table already exists in the database, or whether the program has to create it. I would like to know if there is an SQL query that will tell the program if a certain table exists or not. Any help would be greatly appreciated.

Thanks

cmmrfrds · May 29, 2003

You can find it in the hidden table MSysObjects.

SELECT Name, type
FROM MSysObjects where MSysObjects.Name = "dbo_products" and type = 1

This table has all the object names so check the type also, since a form named "dbo_products" will have a different type. Linked tables will be a different type i.e. 4.

mcklsn · Jun 4, 2003

I am trying to read the MSysObjects table as you suggested, but I get an error saying I don't have permission. How do I go about getting permission to read this table? I'm pretty new at this and I don't have a clue as to how to handle this problem. Again, any help would be greatly appreciated.

cmmrfrds · Jun 4, 2003

It is hard to tell from the information available.

How are you connecting to the Access database. Show your connection string.

Are you passing a login and password in the connection string.

You could try assigning your login to the admin group in the access database.

mcklsn · Jun 4, 2003

Here is a copy of the little test program I'm trying to get running so that I can make sure that I know how to do this:

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>

<%-- This is the java code for the JSP. --%>

<%
String commodity = request.getParameter("select_1&quot

.trim();
String strategy = request.getParameter("select_2&quot

.trim();
String contractYear = request.getParameter("ContractYear&quot

.trim();

%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="post" action="">
We are watching strategy "<%= strategy %>" for <%= commodity %>
in the year <%= contractYear %>
</form>
<%
String sqlTestForTable = "SELECT Name, type FROM MSysObjects where MSysObjects.Name = \"Table1\" and type = 1";
%>
<%= sqlTestForTable %>
<%
try {
String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
String connectionURL = "jdbc

dbc:Commodities";
String userName = null;
String password = null;
Class.forName(driverName);
out.println(" Before conn &quot

;
Connection connection = DriverManager.getConnection(connectionURL, userName, password);
out.println("After conn &quot

;
Statement statement = connection.createStatement();
out.println("Before exec &quot

;
statement.execute(sqlTestForTable);
out.println("After Exec &quot

;
ResultSet rs = statement.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int numCol = rsmd.getColumnCount();
out.println("<tr>&quot

;
if (rs.next())
{
System.out.println("Table found &quot

;
}
else
{
System.out.println("Table not found &quot

;
}
rs.close();
statement.close();
connection.close();
}
catch (Exception e ) {
out.println(" had this problem: " + e.getMessage());
e.printStackTrace();
}
%>
</table>
</body>
</html>
 
</body>
</html>

Does this explain what I'm doing wrong? By the way, I'm logged in as the system administrator for my system, so I would have thought that that would give me the permissions I need. Again, thanks for your help.

cmmrfrds · Jun 5, 2003

The user name and password refer to the access database so it is the permission these have in the access database. I don't use JSP. To find somebody more familiar it is probably better to post the question on the ASP Forum here on tek-tips.

Fubear · Jun 10, 2003

The easiest method to check would be in your Java code

TRY {
//Attempt to open the table
CATCH (SQLExceptionError e) {
//Prompt the usee
}

This is how we handled table validation when I helped a friend write a Java/MySQL system for his Uni dissertation.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Using SQL with MS Access 1

mcklsn

Programmer

cmmrfrds

Programmer

mcklsn

Programmer

cmmrfrds

Programmer

mcklsn

Programmer

cmmrfrds

Programmer

Fubear

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor