Database Search

TechHome

• Access 2000 Advanced DML Transactions
• Access Recordset to CSV File UDF
• Access RunCommand Constants
• ACCESS Table or Query Exists
• Access to Excel via SQL
• Access Visible Problem
• Adding Fields to an Existing Access Table
• Advanced DML Transactions
• Advanced SQL in the Jet 4.0 Provider Example
• Another OLE with Access Example
• Automating Access Tutorial
• Check if Access Table Exists
• Commit Rollback Begin Trans Updatebatch
• Control Access Forms
• Count Access Records
• Csv to Access
• Dealing with ACCESS 97 Field Types
• execute a macro in Access
• Execute a Public function in Access
• Export Access Report in Choice of Formats
• Export MDB to Flat File
• Find Read and Insert Data in Access
• Gather ACCESS Table and Column Information Into Excel
• Get Access Version That Created MDB File
• Get Column Data From an Access Database
• Get Record Count
• How to Use the OpenTable method
• Launch Access Macro from Commandline
• Loop thru a recordset in Access
• Office 2003 Macro Security Issue
• OLE and Access
• OLE Import a table into Access
• OLE Import Table into Access
• Open Access DB with DAO
• Pack an Access Database
• Query and Export Data from Microsoft Access
• Read Write Delete Tables from Access DB
• Repair MDB
• Retrieve Data in Access
• SQL UPDATE Fails with Access
• UDF to Return Record Count
• Updating Table From Filtered Recordset
• Ways to Get Data from and Access file without Access installed

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

How to Find, Read, and Insert Data in Access

Keywords:   INSERT data into access db dao.dbengine.35 DAO.CreateWorkspace

Question:

Answer:

db = "c:\Temp\Test.Mdb"
tablename = "NameTable"
column1 = "FirstName"
column2 = "LastName"
val1 ="Fred"
val2 = "Flintstone"

if !FileExist(db)
	 message("Error","Database file doesn't exist")
	 exit
endif

AccessDB = ObjectOpen("Access.Application")
ADB = AccessDB.Application
ADB.OpenCurrentDatabase(db)
ADB.Visible = @TRUE

ADBCmd = ADB.docmd

ErrorMode(@off)
ADBCmd.OpenTable(tablename)
Errormode(@cancel)
if lastError() == 1261
   ;need to create the table
	SQLstmt = StrCat("CREATE TABLE ",tablename," (",column1," CHAR, ",column2," CHAR)")
	ADBCmd.RunSQL(SQLstmt )
endif

SQLstmt = StrCat('INSERT INTO ',tablename,' (',column1,',',column2,') VALUES ("',val1,'","',val2,'")')
ADBCmd.RunSQL(SQLstmt )
acTable =  0
acSaveYes = 1
ADBCmd.Close(acTable, tablename, acSaveYes)
ADBCmd.Quit

ObjectClose(adbcmd)
ObjectClose(adb)
ObjectClose(accessdb)

Question:

AccessDB = ObjectOpen("Access.Application")
ADB = AccessDB.Application
ADB.OpenCurrentDatabase("Test.Mdb")
ADB.Visible = @TRUE
ADB.UserControl = @TRUE
ADBCmd = ADB.docmd
ADBCmd.OpenTable("Table1")
ADBCmd.GoToControl("Field2")
ADBCmd.FindRecord("Widget")

Answer:

Make sure DAO.DBEngine.35 exists on those computers. Maybe try searching the registry for that application object. Search under HKEY_Classes_Root for DAO.DBEngine....

And make sure that version 3.5 (35 above) is installed. Open up the registry editor on the PC and do a search for:

DAO.DBEngine

If it's installed it will show up as: DAO.DBEngine.35

dao = objectopen("DAO.DBEngine.35")
ws = dao.CreateWorkspace("JetWorkspace", "admin", "")

sqlstr = "CREATE TABLE Contacts ( NAME TEXT(40) ); "
db.execute(sqlstr)

Sample Code to Obtain Information for MS Access Database

;////////////////////////////////////////////////////////////////////////////////
; Obtain Information For MS ACCESS Database
; Takes 3 parameters -    1 - Full Path to MDB File
;                         2 - table or querydef name - Not case Sensitive
;                         3 - mode where 1=Table 2=Query
; Example
; istable C:\TEMP\TEMP.MDB Contacts 2 - searches for query named Contacts
;////////////////////////////////////////////////////////////////////////////////


IF IsDefined(param1)==@NO
   message("Complete Path/File of Access Database Missing"," for example - C:\TEMP\TEMP.MDB")
   exit
Else
   If FileExist("%param1%") == 0
      message("Doesn't Exist or path is Invalid","Cannot Locate %param1%")
      exit
   Endif
Endif


IF IsDefined(param2)==@NO
   message("Parameter Missing","Table or Query You Are Looking For")
   exit
Else
   param2 = StrUpper(param2)
Endif

IF IsDefined(param3)==@NO
   param3 = 1  ; default to Table
Else
   param3 = Int(param3)
Endif

If param3==1
   cVar   = "Table"
Else
   cVar   = "Query"
Endif

msg = "Searching For %cVar% %param2%"


dao = objectopen("DAO.DBEngine.35")
ws  = dao.CreateWorkspace("JetWorkspace", "admin", "")
db  = ws.OpenDatabase("%param1%")

IsFound =@FALSE
msg1    = "Sorry, Not Found"

tbl = db.%cVar%Defs  ; handles either TableDefs or QueryDefs

x=tbl.Count

FOR i= 0 TO x-1
   ivar  = db.%cVar%Defs(i)
   tname = ivar.Name
   If StrUpper(tname)=="%param2%"
      IsFound=@TRUE
      msg1 = "%param2% Was Found"
      break
   Endif
NEXT

message(msg,msg1)

objectclose(db)
objectclose(ws)
objectclose(dao)

exit

Article ID:   W14478

Filename:   Find Read and Insert Data in Access.txt

File Created: 2003:02:03:15:44:10

Last Updated: 2003:02:03:15:44:10