How to build database correctly

[frankt68]

Hello!


I need to create a database of employees and their authorizations for access to individual databases.
Attached is a sample database.test2.accdb

So I have a staff table (tEmployees) and a table with a list of databases (tDatabase). For each employee in the table, I need to add the database's ID to which he/she can access, like in example tAuthorized_access.
I also need to export this to Word, so it will look like in this example
test2.zip.

I don't know much about Access so any help will be appreciated.

[davegri]

test2-davegri-v01.zip
It's a Many-to-Many situation. Altered your tables to accommodate that. Set up form/subform to allow data entry assignments and added a report.
Got rid of the multi-value field. If you're ever tempted to use them again, first come here again for advice.

[orange]

frankt68,
Welcome to the forum. davegri has answered your specific question with a great example.
For more info on database planning and design see links in my signature. There are also samples in the
"Knowledge Base" area of this forum.
Good luck with your project and learning Access.

[frankt68]

Thanx davegri, your template is a very good solution to start with. It solves almost all I need, except for the export to Word.
As I said, I need to export the data for each employee to Word and the databases ID's must be arranged and shown in a table form like in example test2.zip (this is a request of my boss).
Any suggestion?

[frankt68]

frankt68,
Welcome to the forum. davegri has answered your specific question with a great example.
For more info on database planning and design see links in my signature. There are also samples in the
"Knowledge Base" area of this forum.
Good luck with your project and learning Access.

Thanx. I'll certainly have a look.

[davegri]

I need to export the data for each employee to Word and the databases ID's must be arranged and shown in a table form like in example test2.zip (this is a request of my boss).
Any suggestion?

Nope, I don't have a quick answer for that.

[Homegrownandy]

Access to word tables have a lot of formatting issues.

The way I got around it was to have the data from access available in excel. Then you can copy and paste into an existing word table from excel.

The code I have in excel is this:

Sub BetterExcelDataToWord()

Dim objWord As Object, objDoc As Object
Dim strFolder As String, strName As String
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch


ActiveWorkbook.RefreshAll
Dim Ticker As Range
      
strName = Sheet1.Range("i1").Value
strFolder = "\\server\general\RAMS\RAM_RAMS\" & strName


Sheets("PasteSpecial").Activate
Sheets("PasteSpecial").Range("A4:H65").Delete
    
 Application.Wait (Now + TimeValue("00:00:05"))
    Sheets("RISKS").Activate
    Set Ticker = Range(Cells(4, 1), Cells(65, 8))
    Ticker.Copy
    
    Sheets("PasteSpecial").Activate
    Cells(4, 1).PasteSpecial xlPasteValues


Debug.Print strFolder


Set ws = ThisWorkbook.Sheets("PasteSpecial")
lngLastRow = [LOOKUP(2,1/(A1:A65000<>""),ROW(A1:A65000))]
Set objWord = CreateObject("Word.Application")
ws.Range("A4" & ":H" & lngLastRow).Copy


With objWord
  .Visible = True
  Set objDoc = .Documents.Open(strFolder)


  With objDoc.Bookmarks("RISKS").Range
    .Characters.Last.Next.PasteAppendTable


  End With
  .Activate
End With
    
    Set ws = ThisWorkbook.Sheets("methodology")
   
    With objWord.ActiveDocument
     .Bookmarks("methodology").Range.Text = ws.Range("d1").Value
    End With
    Set objWord = Nothing: Set objDoc = Nothing
   
 Application.CutCopyMode = False


Set ws = Nothing
   
   Exit Sub




Errorcatch:
Debug.Assert False
MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description


End Sub

If this is a path you wish to take I can help you out with some specifics.

Andy

[frankt68]

Nope, I don't have a quick answer for that.

Thanx anyway.

[frankt68]

Nope, I don't have a quick answer for that.

Thanx anyway.

[frankt68]

Access to word tables have a lot of formatting issues.

The way I got around it was to have the data from access available in excel. Then you can copy and paste into an existing word table from excel.



If this is a path you wish to take I can help you out with some specifics.

Andy

Thanx Andy, but I decided to first try to do it with aggregated mail merge in Word.

[Homegrownandy]

No problem. Just be aware its not possible to "merge table" as a special paste option from access to Word. If you need to do that then you must paste via excel. Otherwise good luck.

[ssanfu]

I have never tried to automate Word from Access (Excel - yes), but doing a search using keyword "ms word automation using Access", there were lots of returns.
Maybe look at these to start with:

https://www.microsoftaccessexpert.co...utomation.aspx
http://www.brileigh.com/microsoft-ac...rosoft-access/
https://stackoverflow.com/questions/...-word-document
https://answers.microsoft.com/en-us/...6-ce31cb87730e
https://www.techrepublic.com/blog/mi...h-access-data/

[frankt68]

First of all - I apologize for the late reply, but I was busy with other matters.

Secondly - thank you for pointing me in the right direction for searching the solution. I'm not quite there yet, but it's definitely a shift in the right direction.

[frankt68]

First of all - I apologize for the late reply, but I was busy with other matters.

Secondly - Steve, thank you for pointing me in the right direction for searching the solution. I'm not quite there yet, but it's definitely a shift in the right direction.

[ArviLaanemets]

As I said, I need to export the data for each employee to Word and the databases ID's must be arranged and shown in a table form like in example test2.zip (this is a request of my boss).

I.e. a word document for single employee. It look like a task for Mail Merge. I am not sure, is it possible to use Access table or saved query as source for Mail Merge - I have used only Excel for this. But maybe someone here has some experience with this!

And of course you can create an Excel workbook which queries data from access DB, and set the query to be refreshed on opening. And you create a Mail Merge in Word with this Excel workbook as source.