PDA

View Full Version : Solved: ODBC help



CCkfm2000
09-19-2006, 01:16 PM
Hi all..

I have a spreadsheet that pulls data from a database. [Using ODBC]
the problem is that when I refresh the data, the data is wrong.
When I go through the database all the data is right, but when I go through excel the data is wrong.
I have to keep refreshing the data to get it right.
I’ve tried different ways to get the data right, the latest is this code below.

Here is the vba code I’m using.


Dim aa As Integer
Dim bb As Integer
Dim cc As Integer


Sub getdatanew()

Sheets("Inven").Select
refreshdataa

Sheets("DUMMY").Select
refreshdatab

Sheets("Condition").Select
refreshdatac
'a
Sheets("LANE QUANTITY").Select
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
If aa > bb Then
refreshdataa
End If
If aa > cc Then
refreshdataa
End If
'b
Sheets("LANE QUANTITY").Select
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
If bb > aa Then
refreshdatab
End If
If bb > cc Then
refreshdatab
End If
'c
Sheets("LANE QUANTITY").Select
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
If cc > aa Then
refreshdatac
End If
If cc > bb Then
refreshdatac
End If



Sheets("LANE QUANTITY").Select

End Sub
Sub refreshdataa()
Sheets("Inven").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

Sub refreshdatab()
Sheets("DUMMY").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
Sub refreshdatac()
Sheets("Condition").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

the database is used by more than one user so it's changing all time.

any idea? please :help

CCkfm2000
09-27-2006, 02:41 AM
found a solution that is working for now...... :whistle:

thanks for Viewing. :bug:



Dim aa As Integer
Dim bb As Integer
Dim cc As Integer
Dim check, counter
'check = True: counter = 0
Sub loopdata()
Do
getdatanew
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
Do While aa = bb
getdatanew
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
Exit Do
Loop
Loop Until aa = bb
End Sub

Sub getdatanew()
Sheets("Inven").Select
refreshdataa
Sheets("DUMMY").Select
refreshdatab
Sheets("Condition").Select
refreshdatac
'a
Sheets("LANE QUANTITY").Select
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
If aa < bb Then
refreshdataa
End If
If aa < cc Then
refreshdataa
End If
'b
Sheets("LANE QUANTITY").Select
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
If bb < aa Then
refreshdatab
End If
If bb < cc Then
refreshdatab
End If
'c
Sheets("LANE QUANTITY").Select
aa = ActiveSheet.Range("bx75")
bb = ActiveSheet.Range("bx76")
cc = ActiveSheet.Range("bx77")
If cc < aa Then
refreshdatac
End If
If cc < bb Then
refreshdatac
End If

'If a > b Then
'refreshdataa
'End If
Sheets("LANE QUANTITY").Select
End Sub
Sub refreshdataa()
Sheets("Inven").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
Sub refreshdatab()
Sheets("DUMMY").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub
Sub refreshdatac()
Sheets("Condition").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub