Tracker

HOME

TRACKER

Visual Basic

Normalising A Database Efficiently.

Ive written some code to normalise a database but it seems to be slow. I've look for some examples on the web and not found any. There is a wizard in access and that seems to be able to work i out pretty quickly. Where am i going wrong ?
Im sure Its just bad programming technique and my lack of knowledge.
I have one large import table of round 30000 records. plus other to import later

tbl_import_temp
Title, Artist, Brand, Format, TitleID,AristID, BrandID, FormatID

My Nomal tables are ...

tbl_titles
TitleID, Title

tbl_Artist
ArtistID, Artist

and so on.....

here is my code......

Code:Function CheckNormalForm(FieldName, TableName)
        Dim RS As New ADODB.Recordset
        Dim I As Integer
        Dim strValue As String
        Dim arrGetRows()

        strSQL = "SELECT DISTINCT " & FieldName & " FROM tbl_Import_Temp"
        Set RS = cn.Execute(strSQL)
        arrGetRows = RS.GetRows
        RS.Close
        For I = 0 To UBound(arrGetRows, 2) ' cycle through distinct rows
            strValue1 = arrGetRows(0, I)
            strValue = Replace(arrGetRows(0, I), "'", "''")
            strSQL = "SELECT " & FieldName & "," & FieldName & "ID FROM " & TableName & " WHERE " & FieldName & " Like '" & strValue & "'"
            Set RS = cn.Execute(strSQL)
            If RS.EOF Then ' item did not exist in normal table
                RS.Close
                RS.Open TableName, cn, adOpenDynamic, adLockOptimistic, adCmdTable
                RS.AddNew ' add the item
                RS(FieldName) = strValue1
                RS.Update
            End If
            intID = RS(FieldName & "ID") ' get its ID
            RS.Close ' put the ID in the temp import table to create relationships later
            strSQL = "Update tbl_import_temp SET " & FieldName & "ID = " & intID & " WHERE " & FieldName & " Like '" & strValue & "'"
            cn.Execute strSQL
        Next
        Set RS = Nothing
End Function

can anyone give advice on how else to do this ? We import quite a lot of flat files into this database but once a few have been done the process gets slower and slower. I want to get this faster without using SQLServer. (im on access)

Thanks

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread

How To Do This Efficiently?
How Can I Efficiently Search An Array?
Writing Efficiently From VBA To A Worksheet
Efficiently Writing To SQL Table Using VB6
Help With Deleting Efficiently From A Table.
Integer To Binary Efficiently
Getting COM Port Response Efficiently In Vb
Getting Data From EXCEL, Efficiently
Scanning Through Excel More Efficiently
Synchronize Multiple Databases Efficiently.
Efficiently Assemble Conn In Module Help
Efficiently Limit A File To 100 Lines
Stucked---how To Write This Loop Efficiently
Moving Shapes Efficiently - For Games
Best Way To Populate Multiple Combo Boxes Efficiently??
Does Windows Allocate Memory For VB Programs Efficiently?
How To Download Images Efficiently With WebBrowser Control In VB.NET?
Many Checkboxes To Control What Data VBA Outputs- How To Code Efficiently?
Efficiently Loading Stock Price Data Into Excel
Extremely Annoying Database Related Problem! (but Not Database Question)
Calling An Excel Macro From An Access Database That Refers To That Database
Database Ocx To Open Database,Addnew,Delete,Change,Find,
Apogee Database - How To View The Database Structure, Tables, Fields?
How To Link Remote MySQL Database To Local Access Database?
* Combo Box - Nothing Is Read From Database - Connection To Database Incorrect ?*
Error: Unregconize Database Format After Convert Database
VB6 Or VBA - Copy Table Contents From One Database To Identical Database
Transferring VB Project From Access Database To SQL Server Database
Reading ADSI Database With VB && Store In SQL SERVER Database
Export Data From Local Database To Mysql Database On Web
Visual Basic, Read From Database And Write Another Database.
Select And Insert From Shared Database Into The Local Database.
Convert Access Database To Works Database Via Code?
How To Import MySql-database Table To Access-database
How To Locate Database In CrystalReport If Database Location Is Changed !
How To Use An Online Database Like The Internet Movie Database Or The CDDB In A Vb 6
Transfer Database Method - Transfering A Whole Database
Moving Info From Field From Database To Database
Local Database In Sync With Online Database
Database - How Can I Store Images (or Other Files) In A Database?
Moving Info From Field From Database To Database
Database Needs To Be Repaired Or Isn't A Database File ERROR
Creating A Database At Runtime And Saving The Database
Get The Path Of An Access Database From The Database Object?
Convert A SQL Server Database To A Pocket PC Database
Only Info, When Is It Best To Open Database When You Use Database In Network
Addnew And Get Info From Database If The Record Are In The Database HELP
Kill Process In SQL Database (DATABASE) (URGENT PLZ)
Send Database Info From Vb To Web Database?

How To Do This Efficiently?
In my interface I have just finished my 2d version. I am now implementing the 3d version.(same thing but in 3 dimensions) I will basically have to use almost the same forms as I did in my 2d application but with a little modifications. So what I did is I just created new forms and copied the controls over(the text boxes and everything) and I am using new global variables to hold the variables to prevent confusion. Now I have almost 30 forms (and global variables for each form). I am beginning to get the feeling that maybe I am doing this inefficiently.. Could anyone please let me know if there is an efficient way to do this.

How Can I Efficiently Search An Array?
Hi,

I have a program which inserts numbers into an array. It first searches the array to determine if that number is already there. If the number isn't there, it will insert that number into the appropriate position in the array. The array doesn't need to be sorted at any point, because the number is inserted in numerical order, i.e. if the numbers in the array are 2,3,6,9 and the number to be inserted is 7, it will insert it between 6 and 9.

Once the array reaches a significant size - over 1000 entries say - the array searching algorithm becomes very inefficient. It searches the entire array until it finds the number, at which point it stops. Its a brute force approach and I'm sure there must be a more efficient way to do it. I suspect that an efficient algorithm would follow similar logic to a QuickSort routine (which is the fastest sorting algorithm).

Can anyone help?

Cheers.

Writing Efficiently From VBA To A Worksheet
Hi.
I’m trying to make a write operation to a worksheet as quick a possible.
I’ve made an array (one dimensional string, but I can use a Variant if necessary), and I want this to be written to a given row, staring in column A. One way to do it is too loop through the array using

Sheet(“sheet 1”).cells(row, i) = array(i)

in the loop. But as far as I know this is not the most efficient way of doing it.

I have also tried using

Sheets(“sheet 1”).range(<proper dimension>) = array

This works, but some of my array elements contain formulas, and these end up being written in the cell rather than the result of the formula (if array(0) contains (“=Sum(A1:A2)” then that text will appear in the cell rather then the sum of A1 and A2).

Anyone?

Thanks for your time
-Terry

Efficiently Writing To SQL Table Using VB6
Below is a sub that I am using to write data received from a modbus connection to a PLC. I am using the data to keep a history file in a SQL database. To be of any use the data must be gathered very frequently and written in to database very often. Is there a more efficient way of writing this data to SQL than the method below. It looks like my loop times are around 11 to 12 seconds and need to be 1 to 2.

Private Sub WriteDigitalDataToSQL()

Dim ConnectionOne As ADODB.Connection
Dim rstHistory As ADODB.Recordset
Dim ConnectionString As String
Dim SQL As String
Dim i As Integer
Dim DataType As String
Dim ServerName As String
Dim Database As String
Dim Count As Integer

ServerName = "10.1.1.2"
Database = "WIG"

Set ConnectionOne = New ADODB.Connection
ConnectionString = "driver={SQL Server};server=" & ServerName & ";database=" & Database & ";UID=sa;PWD="
ConnectionOne.Open ConnectionString
Set rstHistory = New ADODB.Recordset
rstHistory.CursorType = adOpenKeyset
rstHistory.LockType = adLockOptimistic

i = 0
Count = 0

If Mod1.Result = 0 Then

Do While i < 480

DataType = "Digitals"

If chkVbDigitalTable(i).Value = 1 Then
SQL = SQL + "Insert into history values (" & "'" & Now & "'" & "," & "'" & DataType & "'," & i & "," & VisualBasicReceiveDigitalsTable(i) & ")"
Count = Count + 1
End If

i = i + 1

Loop

rstHistory.Open SQL, ConnectionOne

ConnectionOne.Close

If rtbLog.Text = "" Then
rtbLog.Text = Now & ": " & "Added " & Count & " digital items to the history file"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": " & "Added " & Count & " digital items to the history file"
End If

Else

If rtbLog.Text = "" Then
rtbLog.Text = Now & ": No digital items written...loss of communication"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": No digital items written...loss of communication"
End If

End If

End Sub

Help With Deleting Efficiently From A Table.
Hi all
I have a program that utilises three tables. Table one is a master that is read from, table two is a temp table where only the required info is written to from the master (and data can be added to the records), and the third is for archiving. I am using an apache database with SQL. The fields from the tables are populated into an MSFlexgrid.
I basically have nine fields per record (so one row of a table). Each record can be updated during the course of it's life. The way I am updating a record (if for example I want to add the word "Completed" to a field within the record), is to delete it from the temp table, then re-insert it using info from the program:

Code:
checklist.Execute ("DELETE FROM checklisttemp_table WHERE (job_time = '" & opschecklist.Text2.Text & "' AND sys_name = '" & opschecklist.Text4.Text & "' AND job_name = '" & opschecklist.Text5.Text & "')")

checklist.Execute ("INSERT INTO checklisttemp_table (job_time, sys_name, job_name, job_status, sign_user, jobname_day)values ('" & edit.Label2.Caption & "', '" & edit.Label3.Caption & "', '" & edit.Label4.Caption & "', '" & edit.Text1.Text & "', '" & opschecklist.Text7.Text & "', '" & edit.Text2.Text & "')")
My problem is, I need a more efficient way of deleting the record before adding it again, as if I have two records that are simular, they will both be deleted.
Unless anyone can think of a better way to do this.
AArrgghh...help.......
Thanks
Lee123

Integer To Binary Efficiently
Hi,
I need to do the following as efficiently as possible:

Convert an integer to Binary and put the 1s & 0s into an array.

This is what I have so far:

Code:
' Convert decimal to binary and fill the array
intQuotient = MyInteger

Do While intQuotient > 0
intReminder = intQuotient Mod 2
intQuotient = intQuotient 2

BinaryArray(ctr) = intReminder
ctr = ctr + 1
Loop

How can I do this any faster?
Is there a function to convert Integer to Binary or something?

Getting COM Port Response Efficiently In Vb
I want to know how to catch response from COM port without delay and how to check which port is open

Getting Data From EXCEL, Efficiently
Hello all!

I have experienced a huge time delay in looping through cells in a worksheet to compare, manipulate, get... data.

So I am wondering what is the most efficient way to retrieve data from EXCEL to manipulate, store, compare, etc... without having to loop through the text?

Would it be to turn the spreadsheet into a recordset to compare to another recordset.

Essentially, that is what I am doing. I am trying to compare data from EXCEL to a recordset I retrieve from ACCESS.

Thanks for helping VB City!

Scanning Through Excel More Efficiently
Hey guys,

I'm looking to see if there is a better way to scan through an excel sheet. Right now what I've been doing is:

Code:
For i = 1 To Worksheets(1).UsedRange.Columns.count
For j = 1 To Worksheets(1).UsedRange.Rows.count
If Worksheets(1).UsedRange.Cells(j, i).Text = "mytext" Then
count = count + 1
End If
Next j
Next i

This has proved pretty efficient for me, but there seems to always be someone who has a better way. Any takers?

Synchronize Multiple Databases Efficiently.
4 companies: One database each. (That means 4 DB's)

They all have a table full of vendor information.

I need to check the consistency of the data in the DB's. That is, I need to make sure that every record is present in every DB. While I would be able to do this by looping through each database, reading the primary key and connecteing to each other database to compare the keys, I was wondering if anyone had any insight on a 'better' way of doing this.

Efficiently Assemble Conn In Module Help
I have a connection string public in module. and thay are working fine, code is this:

VB Code:
'in moduleOption ExplicitPublic Const STRING_1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Public Const STRING_2 = "datadbInventory2.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123;"Public Const STRING_3 = "datacashRegister2.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123;"Public userpath1 As StringPublic userpath2 As StringPublic Function strString1(strUser) As StringstrString1 = STRING_1 & strUser & STRING_2 End FunctionPublic Function String1(strUser) As StringString1 = STRING_1 & strUser & STRING_2 End FunctionPublic Function strString2(strUser) As StringstrString2 = STRING_1 & strUser & STRING_3End FunctionPublic Function String2(strUser) As StringString2 = STRING_1 & strUser & STRING_3End Function 'in formPrivate Sub Form_Load()Dim TmpVal As StringDim net As StringDim loca As String TmpVal = GetSetting("protect", "server", "optserver", "True") 'this is option value If TmpVal = "True" Then net = "\" & GetSetting("protect", "CompName", "txtserver", "") 'this return a value "\toshiba1" 'userpath1 = String1(net) userpath2 = strString1(net) MsgBox userpath2Else loca = GetSetting("protect", "LocalDrive", "txtclient", "") 'this return a value "C:" 'userpath1 = String2(loca) userpath2 = strString2(loca) MsgBox userpath2End IfEnd Sub

1.Any one can give me more efficient way to minimize code into 2 string to use in form (example:conn1 for dbinventory2.mdb and conn2 for cashregister2.mdb)

2.after that I want to put down whole code in module into

Any replay is very much appreciate

best regards,

Efficiently Limit A File To 100 Lines
I am developing a small app that will write a line to a file fairly frequently and I'd like to guard against the file getting longer than about 100 lines. Anyone have an efficient method to do that?

Stucked---how To Write This Loop Efficiently
I have a one dimension dynamic array say, my_array(), for example, this time, it's been populated at run time as
my_array(0)=6
my_array(1)=7
my_array(2)=8

obviously, the actual value and length of array can vary at run time
I want to write a select case such as

select case my_number
case 1 to 6
'do something here...
case 7 to 13
'do something here..
case 13 to 20
'do something here...
'hope you could see the pattern here...

how do I write this more effectively and generically

Moving Shapes Efficiently - For Games
Hi there,
I've been trying to develop small games in vb like pacman, tetris etc but I think I may be moving the shapes around the form in an inefficient manner. For instance to move a shape from side of the form to the other I would write the following in a timer procedure (interval set at say 50ms):

Private Sub timer1()
shape1.left = shape1.left + 25
End Sub

Although this works, it doesn't really make the shape move very smoothly. Is there another way that I can move a shape around on a form??

Best Way To Populate Multiple Combo Boxes Efficiently??
Hi everyone,

I have a form which is going to act as a "bulk entry" sheet for the daily work statistics of the users of my application.

It contains a picture box containing a row of controls which repeats 50 times. One of the controls is the "action" box which will be a combobox populated from a database table. Whenever I have used comboboxes like this in the past I have always populated them in the dropdown event. However as this control is repeated and the drop down action is therefore potentially repeated 50 times, it surely doesnt make sense to hit the db and populate the combo for each instance of the control that is used does it??

Can anyone suggest a more efficient/better practice way of doing this please. Should I be considering something like a disconnected recordset (I'd then only hit the db once and the data to populate each instance of the action control would already be there) or is there an even better way to do this ???

Does Windows Allocate Memory For VB Programs Efficiently?
Well, first I apologize for asking a similar question in another thread - but I feel I did not express myself well enough in that one - and this is a really important issue for me and I would really appreciate any feedback.

This started because I fired up the task manager in XP and tried to watch the memory allocation/deallocation for my program. I was not happy with what I saw (looked like my program had a memory leak) so I went through my code and made sure all of my object references were set to Nothing, when I was done with them. Then I set all my form references to nothing and I even put code in the form terminate event as an extra check that my forms were destroyed.

None of this made any difference so I started doing some experiments with very simple programs. I still noticed the following:

1.) When forms are destroyed, the memory is not freed.

2.) Message boxes result in an increase in memory usage with no corresponding decrease.

3.) In my bigger program I noticed "greedy" behavior. In other words, if I created and destroyed a bunch of objects, memory would increase and not decrease, but then if I created the objects again, memory would NOT increase. It was almost like the program was holding on to memory in case it may need it again later. I feel for a true memory leak, this would not happen - memory allocation would just keep increasing.

Does any of this make any sense? Could setting objects to Nothing in VB code not be enough? Could a system call be needed to force the program to release memory?

Could the garbage collector be on strike?

Thanks in advance.

How To Download Images Efficiently With WebBrowser Control In VB.NET?
I want to download all the images inside an HTML page. I have created a WebBrowser control; navigate to a URL; and cast the webpage as mshtml.HTMLDocument:

Code:WebBrowser.Navigate(txtURL.Text)
Dim HTMLDoc As mshtml.HTMLDocument = WebBrowser.Document

After the call to the Navigate function, all the images in that page should be downloaded. My problem is that I don't know how to extract and save the images directly from HTMLDoc. Currently I am using another way to do the task: get the image src from HTMLDoc and use Internet Transfer Control to download the images. This seems stupid since the same picture is downloaded twice.

Some early post suggested to save the whole webpage in order to get the images. But I don't want to do that, coz in the source file the ".src" attributes within the IMG tags will be changed - I want to extract some information from the original ".src" attributes.

Anyone has a solution to this? Many thanx!

Many Checkboxes To Control What Data VBA Outputs- How To Code Efficiently?
I have the following code to create headings on a worksheet based on what the user has selected. What's worse is that there is identical code that actually outputs data on the sheet. Can anyone suggest a more efficient way to do something like this?

If I could cycle through an array of checkbox values perhaps this would speed things up but even then, each if statement would be a bit different. Maybe there is someway to use bitwise AND and ORs in the if statements??

Any ideas would be appreciated- I'm guessing there are many better ways to structure this.

Thanks,

Dave

Code:
If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("U Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("U Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("U Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("U Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("D Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("D Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("D Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("D Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("R Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("R Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("R Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("R Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("L Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("L Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("L Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("L Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("Vertical Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Vertical Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift LSL")

Edit by Moderator:
Please post Excel questions, in the Excel forum.

Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

Efficiently Loading Stock Price Data Into Excel
Hi everyone!

I refer to Peter's article at http://www.eggheadcafe.com/articles/20020108.asp
(quote: "The neat thing about this is that by simply changing the SQL Statement and supplying the name of ANY text file that resides in that folder, you can use the filesystem object (for example) to get a list of the files in the folder, and then read each file into a separate recordset object by simply looping through your array of stock price filenames and dynamically changing the SELECT statement to match on each iteration.")
and I'd like to ask the following:

I am looking for an efficient way to load stock price data into Excel from text files (or any other database stored locally , i.e. I'm not talking bout web queries) for testing purposes.
To be able to measure portfolio risk correctly, the data has to be loaded on a daily basis(*), one by one.

Does anyone have a sample file (e.g. for Excel VBA) which allows to learn the basics?
Would SQL, ADO or an Access database be the most appropriate solution for this?

(*) As I want to avoid recalculation of the same values (indicators, stops, risk, etc.) over and over again, I consider keeping all the calculated data in a database and calculate only one row (the newly added) in Excel.

I hope I explained everything clear enough.

I also post an excellent article on "VBA: Efficiency and Performance". - Enjoy!

Thanks for help and time.
Any help is appreciated.

Regards,
Thomas <tompf@gmx.at>

Extremely Annoying Database Related Problem! (but Not Database Question)
Code:
Private Sub JuicebarSave()

'Start Saving the Juice bar Values!

On Error Resume Next
Call Settings.DBconnect

MyRecSet.Open "Juicebar", MyConn, adOpenDynamic, adLockPessimistic, adCmdTable

X = 0
Do Until MyRecSet.EOF

If Price(X) <> Format(Price(X), "0.00") Then
MsgBox ("Price for Item " & X + 1 & " is not in the right format, all prices must be in the form of currency. (Ex: 10.00 or 1.50)")
Exit Sub
End If

If Color(X).Text = "" Then Color(X).Text = " "
If Item(X).Text = "" Then Item(X).Text = " "
If Price(X).Text = "" Then Price(X) = " "
MyRecSet.Fields.Item("Item").Value = Item(X).Text
MyRecSet.Fields.Item("Price").Value = Price(X).Text
MyRecSet.Fields.Item("Color").Value = Color(X).Text
MyRecSet.Update
MyRecSet.MoveNext
X = X + 1
Loop

MyConn.Close
Unload Me
End Sub

Private Sub StudentStoreSave()

'Start Saving the Student Store Values!

On Error Resume Next
Call Settings.DBconnect

MyRecSet.Open "StudentStore", MyConn, adOpenDynamic, adLockPessimistic, adCmdTable

X = 0
Do Until MyRecSet.EOF

If Price(X) <> Format(Price(X), "0.00") Then
MsgBox ("Price for Item " & X + 1 & " is not in the right format, all prices must be in the form of currency. (Ex: 10.00 or 1.50)")
Exit Sub
End If

If Color(X).Text = "" Then Color(X).Text = " "
If Item(X).Text = "" Then Item(X).Text = " "
If Price(X).Text = "" Then Price(X) = " "
MyRecSet.Fields.Item("Item").Value = Item(X).Text
MyRecSet.Fields.Item("Price").Value = Price(X).Text
MyRecSet.Fields.Item("Color").Value = Color(X).Text
MyRecSet.Update
MyRecSet.MoveNext
X = X + 1
Loop

MyConn.Close
Unload Me
End Sub

Okay so when I save the "Juice bar" settings, the window like, closes for an instant (it flickers) and it seems to have been reopened

But when I save the "Student Store" settings, it Closes the window, and doesnt come back...

I copyed the code from Student store to Juicebar, and still have the problem...

Any ideas?

Calling An Excel Macro From An Access Database That Refers To That Database
hi

i have a macro in excel that inserts some values into a table in a database in access, but i want to make it so i dont have to touch the excel file, instead i have a module in the access file that calls the sub in the excel file, but then i get an error saying that the database is already open....is there a way of getting around this?

thanks

jimmyp

Database Ocx To Open Database,Addnew,Delete,Change,Find,
Database ocx to open database

Most of my program is database

Did somone make OCX to open database so pepole dont need to program same sourse.

Or if somone can help me to make 1 OCX

Please Send me email to ssalomon@simnet.is / siggisal@hotmail.com ,, Subject Database OCX

ssalomon

Apogee Database - How To View The Database Structure, Tables, Fields?
I have to work with an Apogee database but I cant seem to figure out how to view the database structure, tables or fields. I'm looking for a tool or a way that will allow me to view the structure to see which fields I can pull data from.

I'd like to connect to it with VB6 or VB.Net but I cant seem to find out how to connect to it exactly.

I believe that the Apogee database is a product of Binary Research Incorporated. Ive searched google for information regarding the Apogee database but I think its so old that there isnt any.

Thanks for any information.

How To Link Remote MySQL Database To Local Access Database?
i try to create Access DB, and link some tables from remote MySQL DB to this local DB.

it's very easy to do it manually.
could any one tell me how to do it in VB program?
Thank you so much!

* Combo Box - Nothing Is Read From Database - Connection To Database Incorrect ?*
I'm having a problem with reading data froma database to fill a combo box - nothing shows at all - and i'm unsure of what might be the problem.
Hopefully someone could check out my following code and see what needs to be done...also, i have a feeling that my connection to my MS Access database is incorrect and thus might be why i having so many difficulties...
could someone please confirm this with me before i progress any further ?

I would really appreciate that.
here is the code i am working on for the cbobox procedure, and the second example is taken out of what i have in my code module...

Code:
Option Explicit

Dim rs As ADODB.Recordset
Dim Con As ADODB.Connection
Dim ssql As String

Private Sub Form_Load()
Move (Screen.Width - Width) / 2, (Screen.Height - _
Height) / 2

End Sub

Private Sub cboSelector_DropDown()

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
Con.Open strCon

'sql statement to select items on the drop down list
ssql = "SELECT MemberNo FROM Members"
rs.Open ssql, Con

Do Until rs.EOF
cboSelection.AddItem rs("MemberNo") 'Adds MembersNo to dropdown list
rs.MoveNext
Loop

'Close connection and the recordset
rs.Close
Set rs = Nothing
Con.Close
Set Con = Nothing
End Sub

End Sub

and now my code module, what i have of one thus far.if i'm missing anything, please let me know

Code:
Option Explicit

Public rsLibrary As ADODB.Recordset

Public Function readdatabase(strsql As String) As ADODB.Recordset

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Set rsBook = New ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=A:Phase2_ProjectLibrary.mdb; Persist Security Info=False"

cn.Open
With rsBook
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.Source = strsql
.Open
End With
Set readdatabase = rsBook
End Function

Public Sub updatedatabase(strsql As String)
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim comLearn As ADODB.Command
Set comLearn = New ADODB.Command

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=A:Phase2_ProjectLibrary.mdb; Persist Security Info=False"
cn.Open

With comLearn
.ActiveConnection = cn
.CommandText = strsql
.Execute
End With

End Sub

Public Sub fillcbo(ByRef rs As ADODB.Recordset, cbo As ComboBox, strList As String, strNo As String)
While Not rs.EOF
cbo.AddItem rs.Fields(strList)
cbo.ItemData(cbo.NewIndex) = rs.Fields(strNo)
rs.MoveNext
Wend
End Sub

thanks for any efforts in advance!
appreciated as always..

Error: Unregconize Database Format After Convert Database
previously i design my database using Data Manager. after that i wish to set password to the database using Access 2000, but the 'Set Database Password' is disabled. So i convert the database to current database, then i can set the password.

BUT, i come back to my program, all Data Control cannot be refresh. an error says "Unregconize database format....". After sometime of exploring, i change the Connect property from Access to Access 2000, now it can run properly.

BUT, after i compile the program, make a setup package, and reinstall to other PCs, some PC can work but some PC give Error 3170 - Could not find installable ISAM.

anyone can advise?

how can i remain the Data Control's Connect property to Access, so that other PCs can work properly? is it i must convert the database using Access below 2000?

please advise. thank you.

VB6 Or VBA - Copy Table Contents From One Database To Identical Database
Ladies and gentlemen,
first I want to wish each and every one of you a wonderful and prosperous new year.

This is not really a problem. I am looking for an efficient and quick way to copy table content from one database to identical tables in another identical database.

I have two Access databases: "PTSPROD" which is production database and "PTSTEST" which is a test database both databases have 10 identical tables each.

Objective: I need to erase the contents of 5 of the tables in "PTSTEST" then copy contents of 5 similar tables from "PTSPROD" into "PTSTEST".

I could mannually delete the table contents, then copy and paste, but that will be crude.

Question: How can I programatically accomplish this task faster?

Thanks.
tgif

Transferring VB Project From Access Database To SQL Server Database
Hello,

I have been working on a project at my employer to bring a current program that is run by a third-party company, in-house so that we can run it ourselves instead of paying out monthly fees to the third-party company.

The basics of the program are fairly simple. However, after getting the code about 90-95% done, I am realizing that my Access database will quickly become too small to use. I know we have SQL Server on at least one of our servers, and am thinking about trying to use that instead of Access.

My question is, how hard would it be to transfer my project to a SQL Server database instead of an Access database? I am using ADO to connect to my Access database right now. Would I have to make any adjustments to my VB code? I realize some of the SQL in the code will need to be adjusted, but what about my cn. and rs. lines of code (connections and recordsets)?

Shame on me for not realizing Access's size limitations before now. Any information that anyone can provide is greatly appreciated. Thank you in advance.

Reading ADSI Database With VB && Store In SQL SERVER Database
This is first time I m looking for help in any VB forum I hope I can get some useful hint or help regarding my current problem. Let me describe in brief what we want:

We have one vb application which uses the MS-SQL Server Database. We want some changes in the login screen as per our new requirement.

What we want
1)We want to add one form which will show one button called ‘Update Users’ on clicking the button application will read the current users from the ADSI and store/update in the SQL-DATABASE(User Name and password in the existing table in our database).

2) When we will start the application system checks for the current windows user name and password if same user name and password exist in the MS-SQL Server database then there is no need to show login page but if the user name or password not in the database then prompt for the Login Screen

Please help me to find out how to read the ADSI Database using the VB Application email (bestloveh@yahoo.com)

Thanks

Export Data From Local Database To Mysql Database On Web
I have an access database and a clone of that database in mysql format on a webserver. Using vb, is there a way I can populate the mysql database with the data from the access database (It's only one table)? That would save me from having to manually upload a csv to my server all the time...thanks for your help!

Visual Basic, Read From Database And Write Another Database.
I need to read from a table that is located in one database but I need to write some records in other table that is located in other database.

Please/

Select And Insert From Shared Database Into The Local Database.
Hei hei Kiran;
felt happy for your intrest.
Ya, I will explain a bit more about my problem.
Now I have a MS Access 2002 Database which is working perfectly in one system.
Now I have splitted this database with the 'Database splitter' tools menu.I put the backend in a shared folder and front end in every client system and it is working perfectly, without doing any changes.
But as the databse is in the shared folder and the user will have access to this folder only when he has net connection but most of the time they have to work offline.
The user must have access to change only one set of data at a time.
So I have to copy only that set of data from the database in the shared folder to the tables which are in the frontend MDB file.
I think as I don't need to copy all tables, I have to create empty tables which I need to copy and fill the required data into them with VBA code.
for this I need to kow how to connect to the databse in the shared folder and insert into the local database.(of course I have to use the style of connection to connect to the local database as well.)
Is it comprehensible to you , if not i am sorry , please ask where did u not understand.
Any way Thanks a lot for your intrest .

Kiran Karnati.

Convert Access Database To Works Database Via Code?
Does anyone know of a way to convert an access 2000 database to a msworks database via VB6 code? ANY suggestions are welcome. TIA.

How To Import MySql-database Table To Access-database
I'm building a client-database in .NET for my work and I'll have the program copying rows of data from the online MySQL-database to a local offline Access-database. Everything looks alright when opening the local database in Access but when opening it in my app I get an error saying that the index or primary keys will conflict because of duplicates. I have tried endlessly to switch primary keys, creating new ones but nothing seems to help. Any suggestions?

How To Locate Database In CrystalReport If Database Location Is Changed !
Hai,

FrontEnd     : VB 6
Database     : MsAccess
ReportingTool : CrystalReport Designer 9

       I am developing a application in VB 6 with MsAccess as the BackEnd, Now i am having the database and project everything the machines desktop where i develop, After i completed the project if have to put the databse in the Server and create the package and have it in a CD to install in the client machine, at that time how i will change the CrystalReport Designers database path, now while developing in my PC, what i did is, i created the connection through OLEDB and through the CrystalDesigner --> DatabaseExpert i choosed the path of the Database which is in the Desktop in the same directory where the Project is, now after i complete the full project when i place the database in to the server, how the report will be connected to that database in the Serever instead of the connection made in the Desktop. So what i have to do before creating a package.

Before Creating the package i have to place database to the server and change the path or is there anyother way. But after placing the database in the Server if i am going to change the path means then i am loosing all the fields which i placed in the report before, then i need to design the whole report again by placing the fields again. Kindly anyone let me know how to rectify this.

Thankyou,
Chock.

How To Use An Online Database Like The Internet Movie Database Or The CDDB In A Vb 6
Hello I'm trying to create my own organizer of home entertainment, movies, cds, games, exct..., and what I would like to know is how to use the database that already exsist out there to populate the info and load covers all from the online databases like the IMDb or the CDDb, I see there are other programs out there that already use them.
Any help will be greate

Thanx
Venom

Transfer Database Method - Transfering A Whole Database
Hi there, I am fairly new to using VB with access so this may seem a simple thing but im not quite sure what to do.

I am trying to produce a form which will import a complete database, now I thought the best way would be to open a dialog box where the user can search the computer for the database that they wish to import and then to use this path to transfer the database over.

I have come across and looked at the transferDatabase method and getting it to import tables i.e.

Code:
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:DBSNWSales.mdb", acTable, "NW Sales for April", _
"Corporate Sales for April"
(or at least something to that effect )
As I would not know the exact number of tables or even in somecases their names is there anyway for me to import every table without explicitly specifying what the table names are i.e. just simply import the whole database not just a table at a time.

Cheers for your time and help
Rakesh

Moving Info From Field From Database To Database
I have 2 database for storing different information. In my 1st database "CustInfo" I will store the information of the customer in a table call "Infomation". In my 2nd database "data", it stores the customer's transaction under a field "trans" in a table call "transaction". Can I link the info together, which means that whenever the "trans" goes to the "data" database, it also directly goes into "trans" field (new field created) in "Information"? I need to sort them by "indexno" (which both of the database also have). I hope I have not confuse you...

Local Database In Sync With Online Database
I want to develop a tool with a database in local network. The local database can be access or any RDBMS. If I add a record to this database this modification is to be reflected in the online database. We also have an interface in the web for adding or modifying records using form parameters. We also employ cookies to maintain the client user to signin and do these operations. But we need a local java tool to update the local and online database. Is it possible with java or else VB. Please any one help me.

Database - How Can I Store Images (or Other Files) In A Database?
How to do it with Mysql5 on vb6

Database Needs To Be Repaired Or Isn't A Database File ERROR
I have received the following error twice now and am unsure as to the cause:

The database rdstables.mdb needs to be repaired or isn't a database file.

You or another user may have unexpectedly quit Microsoft Access while a Microsoft Access database was open. Do you want Microsoft Access to attempt to repair the database?

Creating A Database At Runtime And Saving The Database
I have a question ok i have a database i want to be created at run time how do i go about doing that in the program its a personal inventory so it has to be saved evertime things are added and updated so if any of you could help me that would be great

Get The Path Of An Access Database From The Database Object?
If I use the following code to open a database in a code module:
Code:Function ConnectMDB(dbPath As String, dbName As String, objTmpDB As Database) As Boolean
    On Error Resume Next
    Set objTmpDB = OpenDatabase(dbPath & dbName, _
        Connect:="Driver={Microsoft Access Driver (*.mdb)};" & _
        "Uid=Admin;Pwd=;")
    If Err.Number = 0 Then
        ConnectMDB = True
    End If
    On Error GoTo 0
End Function

And call it using:
Code: If ConnectMDB(dbPath, dbName, dbKB) = False Then

Can I later get the system path of the database file from the object "dbKB"? Reason: I use the dbKB object thruought my userform which is loaded much later, but I need to know the path of the database without creating an additional userform property.

|
+--JDMils
|
+--VB6
+--VB Dot Net
|
+-- Navman GPS Forums @ http://forum.jdmils.com
|

Convert A SQL Server Database To A Pocket PC Database
Is it possible to convert a SQL Server database (not all tables of it) directly to a Pocket PC database, using Visual Basic 6?

If not, wat are the alternatives, for example using ODBC, Winsock or some sort of ActiveSync API?

Only Info, When Is It Best To Open Database When You Use Database In Network
HI

I am using database on network and the form is wery slow to work when I need to edit database

I open the database in Form_Load
in the form I use Listview to list database and then Form=edit to edit the data
I use Access / .MDB

Is it som rules how to open database when Form_load and close database when form is finish to load to speen up the program ??

Edited by - ssalomon on 8/11/2003 2:29:31 AM

Addnew And Get Info From Database If The Record Are In The Database HELP
HI

When I put ID number into MaskEdBox1 how can I get info from database if the recort are in the database
I only wand to get the info so I dont need to put the same info into the database agen when I do addnew

Table giro

and
Field
Field1 = Textbox1
Field2 = Textbox2

ssalomon

Kill Process In SQL Database (DATABASE) (URGENT PLZ)
Hello,
I've dead lock on my test database and I can't kill the process even when I'm part of sysadmin.
I've also tried the TSQ-transact Kill #ProcessID but nothing

Does anyone know how to kill the process ID?

Your help would be much appreciated.

Many thanks.

Send Database Info From Vb To Web Database?
How to send some local Access97 records from my PC using VB 6.0 to an Access 97 database hosted in a web place, Could I Use ADO in a Form..? .How do I connect...?