Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I load data from a .mdb into a form? (Word 2002)

    Hi,

    Here is my question. Is it possible to develop a VBA Word application that loads custom Form content from an attached database file? (a .mdb).

    The reason I ask is because I have been using another Word application which somehow carries out a similar procedure:

    1. Open up the Word Application.
    2. Click on custom button.
    2. Message displayed 'Getting data from database..."
    3. Custom Form is displayed with database data.

    How do they do this? For them to use the .mdb file to store form data, I thought I would first have to have access installed on my machine, but I don't.

    They use an .mdb, so when you make changes to the form, the changes are written to the .mdb and saved.

    Apologies for the rather vague description, but any help would be much appreciated [img]/forums/images/smilies/smile.gif[/img]

    Thanks, Chris

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How do I load data from a .mdb into a form? (Word 2002)

    When you refer to "changes to the form", are you talking about changing the data? If so, that's quite possible by using a data link to an Access table or query or by populating the form from code using a recordset opened on the Access data and then issuing an update call on the recordset.
    Charlotte

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Microsoft Office (all versions that contain Excel) come with at least one database function library that you can use to read and write MDB files without Access. The older one used DAO and the newer ones use ADO. DAO is simpler, but learning ADO is more useful nowadays. Note, however, that there are some things that are just much easier with Access than with VBA. If it starts getting tricky, you'll want to continue this on the Access board.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    What kind of Word form?

    A user form or a Fill-In Form?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    It is a User Form.

    Thanks.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Also...

    Is this something I could develop within the .dot template using VBA?, or would it be better to use VB and create the whole thing as an add-in?

    Thanks for your time,

    -- Chris

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Yes it is possible to fill a UserForm with data from an MDB.

    You will have to write all the code yourself, since there are no built in controls that can be bound to a datasource. You can either write the code using ADO or DAO.

    You will also need to provide methods of navigating records (First, Previous, Next, Last and New Records), a method to save, a method to edit....

    If you need help on how to do it, then post back.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    You can definately do this in a Word dot.

    IMO, there is no need to go to VB to do this.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Sorry Chris,

    I guess I should refresh my memory with the original post before answering :-)

    Here is a sample mdb and Word doc using DAO (since I don't really know how to use ADO)

    Open the Word doc, and it will open a UserForm that takes data from the mdb and fills in the text boxes on the user form. There is also Previous and Next buttons that will allow you to navigate through the 3 records in the table.

    I wrote this in W2K and A2K, since I don't have access to Office XP, but it should work just fine in 2002.

    One last note, make sure both the doc and mdb are in the same directory.
    Attached Files Attached Files
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Thanks for your help guys.

    I think I am getting the hang of it now. One quick question for you though.

    I am creating the database and table through the Word VBA (as I don't have Access installed on my computer). How do I go about creating a Primary Key? Being used to SQL Server, I have simply used the identity column to do this. Is there an equivalent for Access? How would I create a Primary Key field using the VBA?

    Thanks, Chris

  11. #11
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    A Primary key is just a special index.

    The index's "Primary" property is set to Yes, as well as it's 'Unique" property.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  12. #12
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Hi,

    Below is some code I have written which is supposed to create a table, an index and a couple of fields. I am then attempting to insert a row of data into the database. However, this is not working. Am I using the recommended method to do this?

    Thanks, Chris

    Dim wrkDefault As Workspace
    Dim objDBNew As DAO.Database
    Dim tblDef As DAO.TableDef
    Dim idxScheme As DAO.Index
    Dim objCMD As Command
    Dim fldScheme As DAO.Field
    Dim strSQL As String

    'Create a new encrypted database with the specified collating order.
    Set objDBNew = wrkDefault.CreateDatabase("c:Test DatabaseEZNewDB.mdb", _
    dbLangGeneral, dbEncrypt)

    Set tblDef = objDBNew.CreateTableDef("EZNum")
    Set idxScheme = tblDef.CreateIndex("idxSchemeID")
    Set fldScheme = tblDef.CreateField("SchemeID", dbLong)

    idxScheme.Fields.Append idxScheme.CreateField("SchemeID")
    tblDef.Fields.Append fldScheme
    tblDef.Indexes.Append idxScheme
    objDBNew.TableDefs.Append tblDef

    strSQL = "INSERT INTO EZNum VALUES 'some text1', 'description text'"

    objCMD.Execute (strSQL)

  13. #13
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Chris,

    A few things that I can spot right off the bat:
    1. <LI>You declare wrkDefault but don't set it.
      <LI>You don't set the Primary Key Property of the Index to true
      <LI>You are trying to insert 2 text items into the DB, but there is only one Long Integer field in the table
    I'm not sure what objCMD is supposed to be, but when you are executing an SQL statement, it should be
    objDBNew.Execute strSQL

    Here is a slight Rework of your Table creating code:

    Set objDBNew = Workspaces(0).CreateDatabase("c:documents and settingsusrdesktopEZNewDB.mdb", _
    dbLangGeneral, dbEncrypt)

    <font color=448800>'Create tdf</font color=448800>
    Set tblDef = objDBNew.CreateTableDef("EZNum")
    <font color=448800>'create field</font color=448800>
    Set fldScheme = tblDef.CreateField("SchemeID", dbLong)
    <font color=448800>'Append field</font color=448800>
    tblDef.Fields.Append fldScheme
    <font color=448800>'append field</font color=448800>
    objDBNew.TableDefs.Append tblDef

    <font color=448800>'create index</font color=448800>
    Set idxScheme = tblDef.CreateIndex("PrimaryKey")
    <font color=448800>'Append Field to index</font color=448800>
    idxScheme.Fields.Append tblDef.CreateField("SchemeID", dbLong)
    <font color=448800>'Set the Primary Key property to true</font color=448800>
    idxScheme.Primary = True
    <font color=448800>'Append Index to Table Def</font color=448800>
    tblDef.Indexes.Append idxScheme

    Does this help?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  14. #14
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Bryan, that is a tremendous help, thanks.

    One last question...

    I am getting a syntax error when I attempt to execute the following SQL:

    strSQL = "INSERT INTO EZNumbering VALUES 'Legal1', 'This is a legal'"

    What am I doing wrong here?

    Thanks, Chris

  15. #15
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I load data from a .mdb into a form? (Word 2002)

    Chris,

    Based on your code, the problem may be the fact that you are trying to insert 2 pieces of text data into a single Long Integer Field.

    I'm also not 100% sure if that is the correct syntax for and Update Query.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •