I have an application that uses an Access database. I want the user to be able to create a new database. Using the commands I can create the database and its columns, but I cannot save any records. I do not get any error messages. The puzzling part is that I use the same routine to save records to existing files without any trouble. Or if I create the first record manually, through Access, everyting is fine and new records can be added. The first record is auto increment, if that matters.
When the user creates the database (and before any records are added) close the database and then reopen it and see if that makes any difference.
Example of Create, open and Insert, please. I have usually found that when doing insert, if the number of fields don't match then it gives weird results.
Bill
Quote from: LarryMc on June 02, 2013, 11:04:57 AM
When the user creates the database (and before any records are added) close the database and then reopen it and see if that makes any difference.
That works. Any idea why?
Thanks.
I noticed that when the file is first created, its size is 64KB. After closing the first time (before any records), it goes to 196KB. I guess it does something at that time to allow it to insert records.
The following code is set to create a table from scratch and insert into it right after defining it. This code works.
'=========================
SUB OpenDatabase(),POINTER
'=========================
'DEF dbH, ci, cj, cl, cfnd:INT
DEF dbH, cj, cl, cfnd:INT
DEF pReturn, pdbx:POINTER
DEF flds[16]:STRING
DEF pFlds[16]:INT
DEF dbName = GETSTARTPATH + "Pokemon.mdb":STRING
cname = ""
fname = ""
baseset = ""
cardset = ""
hp = ""
rarity = ""
ctype = ""
natno = ""
fullname = ""
abbrev = ""
setno = ""
setcnt = ""
deck = ""
/* Check to see if Data Base file exists, if not then load 'cards' table */
pReturn = null
IF FileExists(dbName) = 0
dbH = dbCreateMDB(dbName)
pdbx = dbConnect("Microsoft Access Driver (*.mdb)",dbName,";")
IF pdbx = 0
MESSAGEBOX d1,"("+str$(__LINE__)+") Error connecting","Error"
RETURN pReturn
ENDIF
'create table and fields for cards
stmt = "CREATE TABLE cards (recno smallint PRIMARY KEY,cname varchar(70),fname varchar(70),"
stmt += "hp smallint,rarity char(3),ctype char(2),natno char(3),"
stmt += "setno char(4),setcnt char(4),sno byte,vc byte,deck smallint,stage char(1))"
hStmt = dbExecSQL(pdbx,stmt)
IF LEN(dbGetErrorCode(hStmt))
dbFreeSQL(hStmt)
hStmt = 0
dbDisconnect(pdbx)
pdbx = NULL
MESSAGEBOX d1,"("+str$(__LINE__)+") Failed to create DB","Error"
co = 1
OPENCONSOLE
PRINT stmt
RETURN pReturn
ENDIF
dbFreeSQL(hStmt)
hStmt = 0
RecNo = 0
IF OPENFILE(myfile,GETSTARTPATH + "!Pokemon_cards.txt","R") = 0
' Burn header record
READ(myfile,recin)
WHILE (READ(myfile,recin) = 0)
RecNo ++
cfnd = 0
WHILE (cfnd = 0)
cl = INSTR(recin,"'")
IF cl = 0
cfnd = 1
ELSE
recin = MID$(recin,1,cl-1) + "~" + MID$(recin,cl+1)
ENDIF
ENDWHILE
recin += t
cj = Split(recin,t,pFlds)
FOR i = 0 to cj
flds[i] = *<STRING>(pFlds[i])
NEXT i
FOR cl = 6 TO 12
IF LEN(flds[cl]) = 0 THEN flds[cl] = "-"
NEXT cl
RecNo$ = flds[0]
cname = flds[1]
fname = flds[2]
hp = flds[3]
IF hp = "" THEN hp = "0"
rarity = flds[4]
ctype = flds[5]
natno = flds[6]
setno = flds[7]
setcnt = flds[8]
IF hp = "---" THEN hp = "0"
sno = flds[9]
vc = flds[10]
deck = flds[11]
stage = flds[12]
stmt = "INSERT INTO cards (recno,cname,fname,hp,rarity,ctype,natno,setno,setcnt,sno,vc,deck,stage)"
stmt += " VALUES(" + RecNo$ + ",'" + cname + "','" + fname + "'," + hp + ",'" + rarity + "','"
stmt += ctype + "','" + natno + "','" + setno + "','" + setcnt + "'," + sno + "," + vc + ","
stmt += deck + ",'" + stage +"')"
hStmt = dbExecSQL(pdbx,stmt)
IF LEN(dbGetErrorCode(hStmt))
MESSAGEBOX d1,"("+str$(__LINE__)+") Error INSERT " + dbGetErrorCode(hStmt) + " Record " + RecNo$,"Error"
CLOSEFILE(MyFile)
co = 1
OPENCONSOLE
PRINT "Error INSERT ",dbGetErrorCode(hStmt)," Error Record ",RecNo$
dbFreeSQL(hStmt)
dbDisconnect(pdbx)
PRINT
PRINT stmt
RETURN pReturn
ENDIF
dbFreeSQL(hStmt)
ENDWHILE
CLOSEFILE(MyFile)
ENDIF
dbFreeSQL(hStmt)
hStmt = 0
dbDisconnect(pdbx)
ENDIF
RETURN pReturn
ENDSUB
Hope you can use this,
Bill