Deleting all records in access

Hello All,

I am working on a project for a former neighbor. I originally wanted to build a program for him to distribute. He is doing a Geneology research. I wanted to create a database with the family name but realized I could not. So, I want to be able to let him put in the data, export it to text or HTML, then delete ALL the records and start the next family. I have seen many examples but cannot get them to work on VB 2005 Express. Here is the code I have been messing with:

Dim db as Database

dim strSQL as String

strSQL="Delete * from Husband"

conn.execute strSQL

The Dim db and the conn says they are not defined. Where am I messing up Thanks in advance.

Thomas Fairris

Answer this question

Deleting all records in access

satya chappidi
No I didn't. Thank you I will try that. In all the examples I have seen, I never did see them. I will give that a try.

Tried it and will not work. It does not reconize the format of:

dim db as database

Then when I try to run it, it says the sql.execute(strsql) is not a command or something.
Madhavan281981

You'll need this at the top:

Imports System.Data.OleDb

Then you can add this code to any sub or function to execute your sql delete statement

Dim conn as New OleDbConnection("connection string for your access db")

Dim cmdDelete as New OleDbCommand("DELETE FROM Husband", conn)

conn.open()

cmdDelete.ExecuteNonQuery()

conn.close()
bmoyno

Do you have Imports as the first statements preceeding your class declaration

Imports System.Data

Imports System.Data.OleDb

Imports System.Data.Sql

Imports System.Data.SqlClient

Public Class Form1.......
shane_w
Your code looks a lot like ASP not .NET
CloseVision
Actually here is a microsoft knowledge base article on why you are getting that "ISAM" exception. It has to do with your connection string:

http://support.microsoft.com/default.aspx/kb/318161/en-us
April Reagan

Hi Thomas,

You know... I just took a stroll through through the object browser.....

There is no database datatype or object. There are strings where you can get the names of databases, etc but no such thing as a database object.

Can you tell me a little more about what you are trying to do
JWM

In VB 6.0 I used to use the following procedure to kill all records in a database table or view

Public Sub KillAllRecordsInDB(SQLServer$, Catalogue$, TableName$)
Dim loConnection As ADODB.Connection
Set loConnection = New ADODB.Connection
loConnection.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & Catalogue$ & ";Data Source=" & SQLServer$
loConnection.Execute "TRUNCATE TABLE " & TableName$
loConnection.Close
Set loConnection = Nothing
End Sub

maybe this may somehow help... and maybe someone can help convert this to VB 2005 and for an access database. I reused this code in many applications and worked quite quickly.
Ben Post
It keeps throwing this exception

could not find installable ISAM on the conn.open.
samperiau
I am doing it in VB 2005 Express. How would you write the code to handle this I am just going by examples here.
CarBENbased

Coul we see your connection string.
Deleting all records in access

Answer this question