Assign Unique Tracking Number For Re-use

Dave Hawley · Sep 9th 2007

Re: Assign Unique Tracking Number For Re-use

I was thinking along the lines of the file being opened and added to behind the scenes via VBA so the user would not know.

**Reafidy** · Sep 10th 2007

Re: Assign Unique Tracking Number For Re-use

Quote from Dave Hawley

I was thinking along the lines of the file being opened and added to behind the scenes via VBA so the user would not know.

Thanks Dave I was trying not to open the excel file for the reasons I described earlier.

Thanks to everyone that has helped me with this, I think I may have solved the problem if anyone can see potential problems please let me know - I would be very grateful.

This code uses an ADO connection to create and edit a database of tracking numbers in a seperate excel file without opening it:

Code

Private Sub GetNewTrackNo()
    Dim sCon As String
    Dim adoCon As ADODB.Connection
    Dim adoRCS As ADODB.Recordset
    Dim lTrack As Long
    'Open connection
    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Data\Temp\Book2.xls;" & _
           "Extended Properties='Excel 8.0;HDR=Yes'"
    Set adoCon = New ADODB.Connection
    adoCon.Open sCon
    'Set New Recordset
    Set adoRCS = New ADODB.Recordset
    With adoRCS
        'Open Record Set
        .Open "Select * from [Sheet1$]", adoCon, adOpenStatic, adLockOptimistic
        'Get New Tracking No
        lTrack = .RecordCount + 1
        'Add New Tracking
        .AddNew
        .Fields("ColumnA").Value = lTrack
        'Update Tracking Database
        .Update
        'Close Record Set
        .Close
    End With
    'Set All Variables To Nothing And Close Connection
    Set adoRCS = Nothing
    adoCon.Close
    Set adoCon = Nothing
End Sub

Display More

And here is the same code modified to link with access (which is better because it requires less memory to store the data)

Code

Private Sub GetNewTrackNo2()
    Dim sCon As String
    Dim adoCon As ADODB.Connection
    Dim adoRCS As ADODB.Recordset
    Dim lTrack As Long
    Dim i As Long
    'Open connection
    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=\\SAM\TrackIDs.mdb"
    Set adoCon = New ADODB.Connection
    adoCon.Open sCon
    'Set New Recordset
    Set adoRCS = New ADODB.Recordset
    With adoRCS
        'Open Record Set
        .Open "SELECT * FROM Table1", adoCon, adOpenStatic, adLockOptimistic
        'Get New Tracking No
        lTrack = .RecordCount + 1
        'Add New Tracking
        .AddNew
        .Fields("ID").Value = lTrack
        'Update Tracking Databas
        .Update
        'Close Record Set
        .Close
    End With
    'Set All Variables To Nothing And Close Connection
    Set adoRCS = Nothing
    adoCon.Close
    Set adoCon = Nothing
    Debug.Print lTrack
End Sub

Display More

Bill Rockenbach · Sep 10th 2007

Re: Assign Unique Tracking Number For Re-use

hi reafidy,

Just looked at your ADO code. This is exactly what I had suggested. I have coded and tested similar ADO code today. The sample Access data base I have has two tables the first table is the register for new tracking numbers. It uses the Auto "number feature" to assign new tracking numbers. The second table is for tracking the activity as parts are installed, removed, scrapped etc.

Since you are well on the way I’ll stop my development. Good luck.

**Reafidy** · Sep 10th 2007

Re: Assign Unique Tracking Number For Re-use

Hi Bill,

Yeah I figured this was exactly what you are talking about. I always new that it was possible to bring a external database into excel I just was convinced it would be possible/easy to update the external databse while it was closed. Once you said it could be done I was convinced to investigate further and hence the result today. I have noticed that it still takes time to open the ADO connection over the wirless network but using an ADO connection to Access is 90% faster than opening the excel file and changing it and closing it (well in this situation it is anyway).

THanks a lot Bill, Thanks to everyone.

Bill Rockenbach · Sep 10th 2007

Re: Assign Unique Tracking Number For Re-use

Keyword (ignore) = sql

Hello Reafidy,

I think I heard once of a pooled connection that you could leave open for all users. I'll look into that. Although I don't know if your IT dept would appreciate that.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Pooled Connections is the way to go. Faster response time as a connection does not have to be built each time the db is accessed and less overhead on the network. Makes for IT happy campers.

Here are some web sites to look through.

http://www.sql-server-performa…r/asp_ado_pooling_p3.aspx

http://support.microsoft.com/kb/q191572/

http://support.microsoft.com/kb/169470/EN-US/ (frequently asked questions on ADO)

http://msdn2.microsoft.com/en-us/library/ms524771.aspx

http://databases.aspfaq.com/da…ion-string-look-like.html

**Reafidy** · Sep 10th 2007

Re: Assign Unique Tracking Number For Re-use

Well that was pretty successful at giving me a big headache. All this stuff is slightly out of knowledgebase.

THe way I understand it a connection is created at global scope perhaps when the workbook is opened. Then a new connection does not have to be made each time we want to access the database which decreases the load on the network. Then we close the connection when the workbook shutsdown?

But I assume a connection still has to be made by each user right?

Do I have the right idea?

Thanks Bill.

Bill Rockenbach · Sep 10th 2007

Re: Assign Unique Tracking Number For Re-use

My understanding is that multiple users can use the same connection as long as the connection verbiage is exactly the same. The connection will time out (I think the default is 60 seconds), but that can be changed. I’m getting into an area I’ve never been before. Yes, it gave me a headache also.

Perhaps a new thread is order on the Access forum?

**Reafidy** · Sep 10th 2007

Re: Assign Unique Tracking Number For Re-use

Okay good idea, I dont even know how to word the question because I dont know what im talking about :smile: Ill give it a go.

shg · Sep 11th 2007

Re: Assign Unique Tracking Number For Re-use

Hey, Reafidy,

Before you go down the Access path, what's the advantage over using a text file with comma-delimited fields? Does the additional data needed for capture (the stuff other than the sequential number) warrant the use of a database?

**Reafidy** · Sep 11th 2007

Re: Assign Unique Tracking Number For Re-use

No the advantage is that i dont have to open the access file.

And I dont see how you can avoid opening the txt file?

Because I can read the number from the txt file but I dont know how to edit the text file to show that number has been taken.

shg · Sep 11th 2007

Re: Assign Unique Tracking Number For Re-use

Reafidy,

Here's my entry to get sequential numbers from a text file.

Code

Function NewNum(ParamArray vPram() As Variant) As Variant
    ' Returns the next sequential number from a list of numbers in a CSV text file
    ' Adds the new number to the file along with the string data in the ParamArray
    ' If unable, returns "False"
    
    ' network address of the file, minus the ".txt" entent
    Const sFile As String = "C:\Documents and Settings\shg\Desktop\Excel\Reafidy\Reafidy"
    
    Const sExt As String = ".txt"
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    
    Dim sCopy As String     ' name of backup file
    Dim sNew As String      ' temporary name of new file
    Dim sLine As String     ' line buffer
    Dim aStr() As String    ' parsing buffer
    Dim oFS As Object, oFile As Object, oTS As Object ' file system stuff
    
    sCopy = sFile & Format(Now, "-yyyy-mmdd-hhmmss")
    sNew = sCopy & "-new"
    
    Set oFS = CreateObject("Scripting.FileSystemObject")
    
    ' Rename the file. This makes it inaccessible to anyone else, and creates a backup
    On Error Resume Next
    oFS.MoveFile sFile & sExt, sCopy & sExt
    If Err <> 0 Then
        Debug.Print Error(Err)
        On Error GoTo 0
        NewNum = "False" '  meaning file is checked out, come back later
        Exit Function
    End If
    On Error GoTo 0
    
    ' Make a copy appended with "new" and open
    FileCopy sCopy & sExt, sNew & sExt
    Set oFile = oFS.getfile(sCopy & sExt)
    Set oTS = oFile.OpenAsTextStream(ForReading)
    
    ' Read to the last line
    Do While Not oTS.AtEndOfStream
        sLine = oTS.ReadLine
    Loop
    
    ' Extract the number in the last line and bump
    If sLine = "" Then
        NewNum = 1
    Else
        aStr = Split(sLine, ",")
        NewNum = Val(aStr(0)) + 1
    End If
    
    ' Open the new file, append a line with the next sequential number
    '  and other user data
    Set oFile = oFS.getfile(sNew & sExt)
    Set oTS = oFile.OpenAsTextStream(ForAppending)
    oTS.WriteLine Format(NewNum, "00000") & "," & Join(vPram, ",")
    oTS.Close
    
    ' Rename to the original name
    oFS.MoveFile sNew & sExt, sFile & sExt
End Function

Display More

Text file with two examples.
[ss=00001,2007-0910-21:09:00,shg,N01235,manifold
00002,2007-0910-23:09:19,Sky King,N-67832,sump pump]*[/ss]

**Reafidy** · Sep 11th 2007

Re: Assign Unique Tracking Number For Re-use

SHG,

Have tried and tested your code works! Theres some nice code there!!

What I have been doing is loading the databases up to about 20000 parts.

The problem is that when opening file the code takes a long time to execute. Your code takes about 10 seconds. But it is also making backups and things which may not be neccessary so Ill look into it some more.

Thanks a lot for your help much appreciated.

shg · Sep 11th 2007

Re: Assign Unique Tracking Number For Re-use

Quote

What I have been doing is loading the databases up to about 20000 parts.

Is that an integrated part of this problem? It would seem that a parts DB (Access, Excel, whatever) could be copied to each laptop, and synchronized periodically as necessary.

Quote

Your code takes about 10 seconds

Yep -- had no clue about that, and no easy way to meaningfully test. It takes less than a second on my laptop, but that's local, and with a short file.

Reading the source file to the end just to get the last record is surely a waste of network time (read, get data, read, get data, ...). There should be a way to just seek to, and then read, just the last record.

VBA supports several methods for file I/O, and each has its own methods for reading and writing. I'd like to see a tutorial that summarizes them all and explains the rationale for each.

**Reafidy** · Sep 12th 2007

Re: Assign Unique Tracking Number For Re-use

Quote from shg

Is that an integrated part of this problem? It would seem that a parts DB (Access, Excel, whatever) could be copied to each laptop, and synchronized periodically as necessary.

Yes thats another good idea. Im going to follow through with the ADO connection untill I get to the stage where I understand the pros and cons of connecting to ADO then assess which option to go with.

Quote from shg

I'd like to see a tutorial that summarizes them all and explains the rationale for each.

Me too.

Thanks for your help buddy.

Assign Unique Tracking Number For Re-use

Participate now!

Share

Users Viewing This Thread