Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #21 (permalink)  
Old July 12th, 2007, 04:40 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jez,

Think I may have found it, might be one of those Access-being-weird things.

Try:
Code:
sQRY = _
            "INSERT INTO tblCSATAddressA" & vbCrLf & _
"( JobNumber, Address, ProjectID, Project, JobDate, TeamCode, Engineer, Contract, BusinessType )" & vbCrLf &
"IN '" & cTables & "' " & vbCrLf & _
            "SELECT" & vbCrLf & _
            "tblCSATAddressTEMP.[No]," & vbCrLf & _
            "tblCSATAddressTEMP.Description," & vbCrLf & _
            "tblCSATAddressTEMP.[Bill-to Customer No]," & vbCrLf & _
            "tblCSATAddressTEMP.[Scheme Code]," & vbCrLf & _
            "tblCSATAddressTEMP.[Planned Start Date]," & vbCrLf & _
            "tblCSATAddressTEMP.[Team Code]," & vbCrLf & _
            "tblFamilyTree.Engineer," & vbCrLf & _
            "tblFamilyTree.Contract," & vbCrLf & _
                "'" & Me.cboBusinessType & "' AS BusinessType" & vbCrLf & _
            "FROM tblCSATAddressTEMP " & vbCrLf & _
            "LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] = tblFamilyTree.TeamCode"
I think the Fields/IN statements need to be around the other way!

Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
Reply With Quote
  #22 (permalink)  
Old July 12th, 2007, 05:14 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

GET IN THERE!!!!!!!!!
It works :-)

Rob, Thank you!!

I maybe back with other questions later. Its my first time at build database using a front end and back end.

Jez


Reply With Quote
  #23 (permalink)  
Old July 12th, 2007, 05:50 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jez,

Great News! Glad we got there in the end, it can be funny sometimes with Access.
I find that some SQL will work in certain scenarios, but not others?!!?

Of course, if you have any more questions, then please ask.
I remember the first time I did a FE/BE database, was.. interesting!!

Good stuff though, once done its a hell of a lot easier to maintain.
My current project is FE/BE based, user can create multiple project files
(each a DB) and then Access them via a Frontend DB. Kind of emulates the Word/Excel
MDI (Multi-document interface) environment, so maintaining multiple connections/producing
reports etc all within the context of a Project file.

Seems to be working well so far (fingers crossed!)

HTH,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
Reply With Quote
  #24 (permalink)  
Old July 12th, 2007, 06:11 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Rob,

Sounds far more complex than what I am doing :-)

I have been reading about this sort of DB, and found that some people are importing certain tables from the BEdb to the FEdb when the DB opens, I am a little confused by that because, what happens when you have a number of users, could a number of people access the same address and update the same details and then cause issues, is it not better to keep tables in the BEdb and reference them when needed?
Hope I made sense..
Jez

Reply With Quote
  #25 (permalink)  
Old July 12th, 2007, 06:36 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jez,

In principle, you should keep the UI stuff/business logic in the FrontEnd, and all the data in the backend.

However, there have been times when I have imported data into the frontend.
in my previous job, we had a large workflow management system that I had put together, with ~60 concurrent users.
the backend was stored on the network, the frontend was copied to the local machines.
what I ended up doing was importing some data that remained static (daily figures that only updated once a day,
general application data etc) into the front end on load, to keep the data off the wire. I then cleaned this up
when the application was shut down.

However, if you are keeping the front end on the network, then DO NOT import data into it, you almost certainly
will have problems with data collision/locking issues etc.

Hope this helps, as always any probs, then please ask.

Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
Reply With Quote
  #26 (permalink)  
Old July 12th, 2007, 07:45 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Rob,

I understand now. In my case the only static tables are reference tables, the others are constantly updated ie tblCSATAddressA.
Also the FrontEnd DB will be stored on the User local machine, and the BackEnd will be on the network.

Ok, now as you gathered from previous threads, I dont quite get the whole recordset thing.

From this example can you explain the recordet info
Sub OpenData()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sQRY As String
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
        "Data Source=" & cTables
    DoCmd.SetWarnings False

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
        "Data Source=" & cTables

    sQRY = "SELECT * FROM tblCSATAddressA"
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    Exit Sub
End Sub

Thanks, Jez

Reply With Quote
  #27 (permalink)  
Old July 12th, 2007, 08:19 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jez,

Since we are heading off-topic now, if you don't understand my reply, can you re-post into a new topic please?
(makes it easier for other people to find help if they need it)..

OK, Recordsets.
Now, I'll try to explain this simply, if anything is confusing then please shout!!

Recordsets are containers for Data. A Recordset is able to access the data stored
within a database (be it an Access database, or data on SQL Server) and store it in
a format that allows the data to be examined, and manipulated if required.

Recordset's are part of the ADO Package, which is designed by MS to act as a common platform
for accessing data. The ADO package contains (this list is far from exhaustive!)
• Recordsets - Containers to hold, examine, traverse and update data.
    • Records - These are the rows within the Physical Table
    • Fields - These are the columns within the Physical Table
• Connections - Contains metadata about a connection, and maintains a "path" to a database.
    • ConnectionString - The String specifying the location/username/password etc to access a database

Using ADO allows you to programmatically work with data. ADO cuts out a LOT of the fat when working
with data, and provides a pretty extensive model to get feedback on what a database is doing.

For example, if you have a Table in the current database, with a single field "Test" in a Table called "MyTable",
you could print the value of "Test" for each record via ADO like so:
Code:
Sub RSDemo()

    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection 'Create a Connection to allow the Recordset Access to the data within

    Dim rs As New ADODB.Recordset       'Declare a Recordset for use.
    rs.LockType = adLockReadOnly        'Specify the Locking to be placed on the table, since we
                                        'are not manipulating data, choose ReadOnly.

    rs.CursorType = adOpenForwardOnly   'The CursorType specifies how we want to traverse the Recordset
                                        'simply put, the most restrictive is faster. ForwardOnly allows
                                        'us to move through a Recordset in a Top>Bottom manner.

    rs.ActiveConnection = cnn           'Set the Connection to the connection previously created (Current DB)

    rs.Open "SELECT Test FROM MyTable"  'Open the Recordset up with some SQL - The actually selects the data.

    Do While Not rs.EOF                 'Loop until the EOF (End of Recordset/Table) is encountered.
        Debug.Print rs.fields("Test")   'Print the value of the "Test" field
    Loop

    'Clean Up Object variables.
    Set cnn = Nothing
    Set rs = Nothing

End Sub
I hope that provides some insight into how ADO Recordsets/Connections work, as well as
give an indication of the power that they bring (e.g. The connection could be a connection to ANY database).

Regards,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
Reply With Quote
  #28 (permalink)  
Old July 26th, 2007, 10:43 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have this code below that works, and imports the data into the relevant tables. on the line DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath, True How can I specify that it import from a range. I have a Range called ImportData on the excel sheet?

Private Sub cmdImport_Click()
    On Error GoTo Err
    Dim cnn As ADODB.Connection
    Dim sQRY As String
    DoCmd.SetWarnings False
    strTempTable = "tblCSATAddressTEMP"
    strFilePath = Me.txtFilePath
    'Check that BusinessType has been selected- quit if not
    If IsNull(Me.cboBusinessType) Or Me.cboBusinessType = "" Then
        MsgBox "You must select a Business Type from the dropdown list", vbExclamation, cApplicationName
        Exit Sub
    End If
    If VBA.Len(strFilePath) <> 0 Then
        'Import the spreadsheet
        DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath, True
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
        "Data Source=" & cTables
            'insert new addresses into tblAddress
        sQRY = _
            "INSERT INTO tblCSATAddress" & vbCrLf & _
            "( JobNumber, Address, ProjectID, Project, JobDate, TeamCode, Engineer, Contract, BusinessType )" & vbCrLf & _
            "IN '" & cTables & "' " & vbCrLf & _
            "SELECT" & vbCrLf & _
            "tblCSATAddressTEMP.[No]," & vbCrLf & _
            "tblCSATAddressTEMP.Description," & vbCrLf & _
            "tblCSATAddressTEMP.[Bill-to Customer No]," & vbCrLf & _
            "tblCSATAddressTEMP.[Scheme Code]," & vbCrLf & _
            "tblCSATAddressTEMP.[Planned Start Date]," & vbCrLf & _
            "tblCSATAddressTEMP.[Team Code]," & vbCrLf & _
            "tblFamilyTree.Engineer," & vbCrLf & _
            "tblFamilyTree.Contract," & vbCrLf & _
                "'" & Me.cboBusinessType & "' AS BusinessType" & vbCrLf & _
            "FROM tblCSATAddressTEMP " & vbCrLf & _
            "LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] = tblFamilyTree.TeamCode"
            CurrentProject.Connection.Execute sQRY
        End If
        DoCmd.DeleteObject acTable, strTempTable
        MsgBox Me.cboBusinessType & " Data has been imported", vbExclamation, cApplicationName
        cnn.Close
        Set cnn = Nothing
    Exit Sub
Err:
    MsgBox VBA.Error$
End Sub


Reply With Quote
  #29 (permalink)  
Old July 26th, 2007, 11:23 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Jez, this is off-topic, can you repost to another thread?

Sorry I dont mean to be pedantic, but it helps other people find relevant information quicker!

Regards,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Error: Microsoft Jet Engine asters ASP.NET 1.0 and 1.1 Professional 2 April 3rd, 2007 09:33 AM
Microsoft JET Database Engine error '80040e14' aprnip Classic ASP Databases 2 November 15th, 2004 02:06 AM
Microsoft JET Database Engine error '80040e07' aprnip Classic ASP Databases 1 October 22nd, 2004 07:19 AM
Trying to find the Microsoft JET OLEDB download marthaj Access ASP 3 September 23rd, 2003 11:53 PM
Microsoft JET Database Engine error '80040e10' nvillare Classic ASP Databases 1 August 20th, 2003 12:09 PM



All times are GMT -4. The time now is 07:45 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.