Read from / Write to Access 2016 Database

[grahamP6L9M]

Afternoon All

Can somebody please assist with, or point me in the direction of some examples on how to successfully connect to a 2016 Access Database from 2019 VBA?  I'm pulling different pieces of code together from various Excel forums but not having complete success, primarily due to ignorance.

I've sorted the ADO references ok, it's the actual syntax and procedures I'm struggling with.

My database has a number of linked tables with primary and foreign keys, which essentially create a drawing tracker, allowing me to log the details of drawings going out.  The tables are linked thus:

Client -> Project -> Discipline -> Drawing -> Revision Info

All the info is known (so client name, project name, etc) and I need to be able to access each table and the correct record in turn, so I can then record the info in the final table of Revision Info (date, rev number etc)

Additionally, I need to be able read out info from a list of drawings when the user selects Client -> Project etc from the combo boxes on a userform.  So when all combo boxes are set, a listbox is filled with available drawings and when one is selected, it's revision history stored in the database is presented in the userform.

Any help with the above would be most gratefully received.

Thanks very much.

P.S. Has to be VBA, can't use .NET and haven't the time to learn 😞

[nucliabrasil]

My friend, I've had a lot of trouble with this. Just for information. On 32-bit machines the connection to ADO is quiet, but on 64-bit machines and I believe that is your case, you will hardly be able to do it. I managed to get around connecting to mysql. Access 2016 is very difficult. I've tried it anyway and used multiple drivers and connection types and nothing worked.
Maybe some expert here from the forum can give you a hint to connect access 2016, I honestly only managed with mysql.

[grahamP6L9M]

Hi

Thanks for the reply.  I've had partial success, setting up a connection and opening / closing the database. It's interacting with it once it's open that I'm struggling with.

The code I've got that works is as follows.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim DBFullName As String

DBFullName = "<my database path & name plus file extension of .accdb>"

cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Open DBFullName

cn.close

I can tell that this works as it creates the ".!accdb" in the same location as my database, so it must be open.  It's the recordset part I need help with, and how to go navigate through the various linked tables.

Cheers

[nucliabrasil]

You need creat recordsets to navegate on table datas.

Inside module, creat  conections and recordsets.

A simple example:

Inside module:

Option Explicit

Public dbconn As Adodb.Connection  ' to conect

Public rsX As Adodb.Recordset           ' to view table

Sub Conectar()

Set dbcon = CreateObject("ADODB.Connection")

Set dbcon = CreateObject("Adodb.Connection")
dbcon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\dbxxx.mdb;Jet OLEDB:Database Password=xxxxxx;"
dbcon.Open

recordsets...

Set rsX = CreateObject("Adodb.Recordset")
rsX.CursorLocation = adUseClient

End Sub

Inside forms: Accessing table datas

Conectar
Set rsX.ActiveConnection = dbCon
rsX.Open "select * from yourtable", dbCon, adOpenStatic, adLockOptimistic
rsX.Filter = "fieldtable='" & txtY.text & "'"  '<< In case for seach some data inside table.

rsX.fieds("fieldtable")= txtY.text

I holp help you.

So, you can use this forms do manipulate datas.

rsX.add     

rsX.update

rsX.delete 

[grahamP6L9M]

Hi NucliaBrasil

Thank you very much for your input, that helps very much indeed.

If at all possible, can you please share some more guidance on how to filter through the tables?  I'm having trouble with the "fieldtable" part.

I have a table at the top of my database which is called "tblClients" and which has just two fields called "ClientID" and "ClientName".  When I'm querying the database, I know the ClientName (which for arguments sake we can call "Client1") so need to go straight to that entry in tblClients, then access the next table down with is called "tblProjects" and pull up a list of all project names (from the "ProjectName" field) for that client ("Client1").

I hope that makes sense.

Thanks very much for your help so far.

Graham

[nucliabrasil · Accepted solution]

Go on ...

'adding project inside table tblProject...
Conectar
Set rsProj.ActiveConnection = dbCon
rsProj.Open "select * from tblProject", dbCon, adOpenStatic, adLockOptimistic

rsProj.add
txtProjectName.text = rsProj.field("ProjectName")
txt.ClientName.text = rsProj.field("ClientName")
rsProj.update

'Searching all project's name and loading a combobox...
Conectar
Set rsProj.ActiveConnection = dbCon
rsProj.Open "select * from tblProject", dbCon, adOpenStatic, adLockOptimistic

cboCN.clear
do until rsProj.eof

cboCN.additem rsProj!ProjectName ' < loading all projects names inside combobox

rsProj.MoveNext
loop

Now, you will get the client's names and searching by ProjectName... on event "cboCN_click"...

Conectar
Set Proj.ActiveConnection = dbCon
rsProj.Open "select * from tblProjects", dbCon, adOpenStatic, adLockOptimistic
rsProj.Filter = "ProjectName='" & cboCN.text & "'"

cboCLI.clear
do until rsProj.eof

cboCLI.additem rsProj!ClientName ' < loading all client's name inside combobox

rsProj.MoveNext
loop

[grahamP6L9M]

Hi

Just so you're aware, I've got it working really well now, thanks to your help.  I'm loving the ease and simplicity of the SQL part.

Thanks for all your help!

---

@nucliabrasil wrote:

Go on ...

'adding project inside table tblProject...
Conectar
Set rsProj.ActiveConnection = dbCon
rsProj.Open "select * from tblProject", dbCon, adOpenStatic, adLockOptimistic

rsProj.add
txtProjectName.text = rsProj.field("ProjectName")
txt.ClientName.text = rsProj.field("ClientName")
rsProj.update

'Searching all project's name and loading a combobox...
Conectar
Set rsProj.ActiveConnection = dbCon
rsProj.Open "select * from tblProject", dbCon, adOpenStatic, adLockOptimistic

cboCN.clear
do until rsProj.eof

cboCN.additem rsProj!ProjectName ' < loading all projects names inside combobox

rsProj.MoveNext
loop

Now, you will get the client's names and searching by ProjectName... on event "cboCN_click"...

Conectar
Set Proj.ActiveConnection = dbCon
rsProj.Open "select * from tblProjects", dbCon, adOpenStatic, adLockOptimistic
rsProj.Filter = "ProjectName='" & cboCN.text & "'"

cboCLI.clear
do until rsProj.eof

cboCLI.additem rsProj!ClientName ' < loading all client's name inside combobox

rsProj.MoveNext
loop

---