Constructive advice wanted

[Wes via AccessMonster.com]

Very new to VB coding and looking for some insightful advice so that I don't
get into very many bad coding habits.

When I first jumped into this project I added autonumber to the table
"GroupFiles" so that there would be a key field, quickly found out that these
files would max out the 2 gig limit of Access very quickly (and these are the
small ones). Our CAD administrator then offered some space on the Oracle
server to hold the table. I accepted quickly dumped/exported Access table
into the Oracle table, then I found out that Oracle does not do autonumber.
This autonumber "problem" shows up by me having to go through some goat ropes
listed below. Thinking of just doing away with autonumber, but then I would
not have a "indexed (duplicates not OK)" field in the Oracle table, even
though I can't imagine what I would use it for. It does nothing but take up
space, but I have read that it is good database practise to have a key field.

Any thoughts would be appreciated...



Function Start_GF_Import()

'I am dealing with very large files/databases. So timing the 'procedure
satifies my curiosity.
Dim Msg, Style, Title, Response
Dim TimeStart As Single
Dim TimeEnd As Single
Dim timeElapsed As Single


'Have to reset table due to Oracle not having autonumber.
Msg = "Database MUST be compacted before import. Continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "This is crucial"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
TimeStart = Timer
GF_Proc
TimeEnd = Timer
MsgBox ("Import finished")
End If
timeElapsed = Format(TimeEnd - TimeStart, "Fixed")
MsgBox ("The procedure took " & hns(timeElapsed) & " to run.")

End Function

Sub GF_Proc()


'Need to process raw data to comply with database standards. Actually,
getting field names right by replacing blanks with underscores. I wrote an
Excel macro to do this.
Dim myXL As Object
Dim mySS As Object
Set myXL = CreateObject("Excel.Application")
Set mySS = myXL.Workbooks.Open("C:\mypath\Config GFs Macro.xls")
myXL.Run "StartMe_Conf_GFs"
mySS.Close
myXL.Quit
Set mySS = Nothing
Set myXL = Nothing


'Have to get last "autonumber" from Oracle table.
DoCmd.OpenQuery "GFs_Sorted_Query", acViewNormal, acReadOnly
DoCmd.GoToRecord acDataQuery, "GFs_Sorted_Query", acLast
DoCmd.RunCommand acCmdCopy
DoCmd.Close acQuery, "GFs_Sorted_Query", acSaveNo

'Paste last "autonumber" from Oracle table into integer field in first
temp table.
DoCmd.OpenTable "z_groupfiles03", acViewNormal, acEdit
DoCmd.RunCommand acCmdPaste
DoCmd.Close acTable, "z_groupfiles03", acSaveYes
DoCmd.SetWarnings False

'Append single record with integer field into second temp table that has
autonumber.
DoCmd.OpenQuery "zGFs03_to_zGFs"
DoCmd.SetWarnings True

'Import processed data into second temp table.
ChDir "c:\temp"
myFile = Dir("*.xls")
Do Until myFile = ""
DoCmd.TransferSpreadsheet acImport, 8, "z_GroupFiles", myFile, True
Kill myFile
myFile = Dir("*.xls")
Loop

'Delete first autonumber in second temp table that was appended from
Oracle table
DoCmd.SetWarnings False
DoCmd.OpenTable "z_groupfiles", acViewNormal, acEdit
DoCmd.GoToRecord acDataTable, "z_groupfiles", acFirst
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acTable, "z_groupfiles", acSaveYes

'Append data from second temp table into main Oracle table.
DoCmd.OpenQuery "zGFs_to_GFs_Append_Query"

'Delete data from first and second temp tables.
DoCmd.OpenQuery "z_GFs_Delete_Query"
DoCmd.OpenQuery "z_GFs03_Delete_Query"
DoCmd.SetWarnings True

End Sub


Swimming in the ocean of coding,

Wes

 

[strive4peace]

Hi Wes,

It is a good idea to have a unique identifier for each record.

Are you mostly entering records by importing them or do you
use forms to put them in?

If you use forms, you can use the BeforeInsert event to
assign the next number.

me.IDfield = dMax("IDfield","Tablename") + 1

How is your database getting so big? Are you storing OLE
fields? If so, consider storing the filepath and filename
and rendering the files when you look at the record.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[rhann]

another good tip for spell checking your code is to make all ordinarily two
worded
phrase oneword and the making the first letterof each word a capital

eg

customer id becomes CustomerId as ms access converts a lowercase phrase to
look like you orgionally typed it

 

[wes via AccessMonster.com]

All the records are imported. The files are are actually log files off a
radio system and are computer generated.

The logging computer generates 4 files per day. Two of the files are very
small, one is medium, and the forth is huge (between 150k to 200k records per
day).

I am not sure what you mean about OLE, something to do with objects, like
pictures, I think. But this is not the case. Just pure data is being imported
into the table.

With the forth file, named SystemFiles, it takes about 45 minutes to import
one day worth of data. I am thinking of turning off the indexes before I
import the next batch to see if this will speed things up, then turn the
indexes back on.

The unique field, which I don't think I will ever use, is a hassle to keep
straight, but our database guru here and you say that a unique field is good
practise. So I will keep the "Autonumber" unique field.

If you have any more question, thoughts or questions please do not hesitate
to post them.

Wes

Hi Wes,

It is a good idea to have a unique identifier for each record.

Are you mostly entering records by importing them or do you
use forms to put them in?

If you use forms, you can use the BeforeInsert event to
assign the next number.

me.IDfield = dMax("IDfield","Tablename") + 1

How is your database getting so big? Are you storing OLE
fields? If so, consider storing the filepath and filename
and rendering the files when you look at the record.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Very new to VB coding and looking for some insightful advice so that I don't
get into very many bad coding habits.

[quoted text clipped - 104 lines]

Wes

 

[RD]

Actually, Oracle *does* do Autonumber ... it just doesn't call it that. In
Oracle it's called "Sequence". See this:
http://www.techonthenet.com/oracle/sequences.php



HTH,
RD

 

[samwise2u via AccessMonster.com]

Thanks for the info. Checked the web sight, it looks promising so I booked
marked it.

Trying to get a brand new server with Oracle Standard Edition One right now.
The Oracle I have access to is very limited, so trying to get one I can have
a little bit more say so with.

Again, thanks all for the insights.

Wes

Actually, Oracle *does* do Autonumber ... it just doesn't call it that. In
Oracle it's called "Sequence". See this:
http://www.techonthenet.com/oracle/sequences.php

HTH,
RD

Very new to VB coding and looking for some insightful advice so that I don't
get into very many bad coding habits.

[quoted text clipped - 104 lines]

Wes