Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Can this be done in access ?

IlmgardIlmgard Member Posts: 2
Suppose we have 3 warehouses: A, B and C and each warehouse has 5 Books.
Now, a customer orders 3 books, so we substract quantity in warehouse A to 2. The second customers also orders 3 books, but since in warehouse A we only have 2, and sending items from warehouse B (or C) takes a few days, we have to ask for a choice wheter the customer wants partial delivery or just wait until the books are on stock.

I suppose doing this in VB is a lot easier but I have no clue how to get data from access/excel to VB.

Thank you very much.

Comments

  • dokken2dokken2 Member Posts: 532
    : Suppose we have 3 warehouses: A, B and C and each warehouse has 5 Books.
    : Now, a customer orders 3 books, so we substract quantity in warehouse A to 2. The second customers also orders 3 books, but since in warehouse A we only have 2, and sending items from warehouse B (or C) takes a few days, we have to ask for a choice wheter the customer wants partial delivery or just wait until the books are on stock.
    :
    : I suppose doing this in VB is a lot easier but I have no clue how to get data from access/excel to VB.
    :
    : Thank you very much.
    :


    You can use DAO or ADO to connect VB to an Access database, see example below. Either VB or Access can do what you need, the real effort is to write the code to handle purchasing the books from the 3 warehouses. I suggest you write your logic in plain english 'pseudocode' to work thru the various cases you'll need to handle, then it will be easier to write the code.

    [code]
    Option Explicit

    '7/1/2005 - ADO CONNECTION TO A ACCESS DB
    'PASS [False] for unsecured db
    'PASS [True] for a secured db - requires acccount/password
    Public Sub AdoConnectSecureDB(ByVal SecureDB As Boolean)
    'SET DB AND SYSTEM PATHS
    Const cMyDBpath = "C:Tempdb1.mdb"
    Const cMySysMDW = "C:TempSystem.mdw"

    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim sCon As String
    Dim MyAccount As String
    Dim MyPassword As String
    Dim errCount As Integer

    On Error GoTo erh

    Select Case SecureDB
    Case Is = False
    MyAccount = "Admin"
    MyPassword = ""
    Case Is = True
    MyAccount = "[ACCOUNT]"
    MyPassword = "[PASSWORD]"
    End Select

    'CONNECTION STRING
    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "User ID=" & cMyAccount & ";Password=" & cMyPWD & ";" & _
    "Data Source=" & cMyDBpath & ";" & _
    "Persist Security Info=False"
    'SECURE DB W/ SYSTEM.MDW?
    If SecureDB = True Then sCon = sCon & ";Jet OLEDB:System database=" & cMySysMDW

    'CREATE NEW CONNECTION
    Set cn = New ADODB.Connection
    'OPEN CONNECTION WITH CONNECTION STRING
    cn.Open sCon
    'CREATE NEW RECORDSET
    Set rs = New ADODB.Recordset
    'OPEN RECORDSET
    'READ/WRITE
    rs.Open "TABLE1", cn, adOpenDynamic, adLockOptimistic, adCmdTable
    'READ ONLY
    'rs.Open "TABLE1", cn, adOpenStatic, adLockOptimistic, adCmdTable

    'TRAVERSE RECORDS
    rs.MoveFirst
    Do While Not rs.EOF
    MsgBox rs!FIELD1
    rs.MoveNext
    Loop

    xit1:
    'CLOSE
    rs.Close
    cn.Close
    xit2:
    'RELEASE RESOURCES
    Set rs = Nothing
    Set cn = Nothing
    Exit Sub

    erh:
    MsgBox Err.Description, vbExclamation, Err.Number
    errCount = errCount + 1
    If errCount > 1 Then Resume xit2 Else Resume xit1
    End Sub
    [/code]
Sign In or Register to comment.