topic Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database in Inventor Programming Forum

Connect Inventor Professionnal 2019 to MySQL8.0 Database

Anonymous — Wed, 15 May 2019 07:28:17 GMT

Please, i'm looking to connect Inventor Professionnal 2019 to a DATABASE in MySQL 8.0 using a VBA code.

i maked some tests in Excel and it works.

but it doesn't work using the same Code in VBA editor of Inventor.

i cheked the reference of the editor. their are the sames in Excel. So i don't know how resolve the problem.

this is my VBA code :

Private Sub CommandButton1_Click()
UserForm
End Sub

Private Sub UserForm()
   Dim Cn As ADODB.Connection
  Dim Server_Name As String
 Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
   Dim SQLStr As String
  Dim rs As ADODB.Recordset
 Set rs = New ADODB.Recordset
    Dim Num As String

    Server_Name = "127.0.0.1"
    Database_Name = "base"
    User_ID = "root"
    Password = "m02pas"

    Num = TextBox1.Value

    SQLStr = "SELECT numero FROM table01 WHERE numero LIKE '" & Num & "%'"

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={MYSQL ODBC 8.0 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    rs.Open SQLStr, Cn, adOpenStatic

    a = rs.GetRows
    ListBox1.ColumnCount = UBound(a, 1) + 1
    ListBox1.List = Application.Transpose(a)
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

Thank you.

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

Mark.Lancaster — Wed, 15 May 2019 12:18:05 GMT

@Anonymous

Welcome to the Autodesk User's Community..

You want to be asking this over in the Inventor Customization forum https://forums.autodesk.com/t5/inventor-customization/bd-p/120

I will have the moderator relocate your posting there to best suit your needs.

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

Anonymous — Wed, 15 May 2019 12:19:26 GMT

Thank you verry much

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

Frederick_Law — Wed, 15 May 2019 13:45:56 GMT

First, what doesn't work?

Second, you just let everyone know your username and password.

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

Anonymous — Wed, 15 May 2019 13:56:28 GMT

It's a local DATABASE so dosn't matter if i give the password or not.

the VBA code that i gave, i'm using it on excel to look for the parts (Num) in the database (MySQL Server). it works...

now i'm trying to use the same programme in Inventor to do the same work (to show in the listbox1 all the number of parts which start with the value of the textbox1.value. but it doesn't work.

is there a configuration to make befor run the programme.

thanks.

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

Frederick_Law — Wed, 15 May 2019 14:58:23 GMT

Add stop and watch rs and a.

See if they get value from SQL.

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

Anonymous — Thu, 16 May 2019 07:13:18 GMT

Thank you for your answer.

i'm biginner too so can you tell me how to writ it in the code please.

Thank you in advance.

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

BrianEkins — Tue, 21 May 2019 06:56:35 GMT

My guess is that you have a 32-bit version of Office installed and the library you're referencing to access the database is also 32-bit. Inventor is 64-bit and uses the 64-bit version of VBA. It's possible to install a 64-bit version of Office. I don't know about the database library.

Re: Connect Inventor Professionnal 2019 to MySQL8.0 Database

Anonymous — Tue, 21 May 2019 07:40:22 GMT

Thank you verry much for your answer.

the problem was resolved by @gmassart

this is the solution :

'Déclaration de la variable de connexion
    Dim cnx As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnx = New ADODB.Connection
    Set rst = New ADODB.Recordset
    'Définition de la chaîne de connexion
    'cnx.ConnectionString = "UID=" & NomUtilisateur & ";PWD=" & MotDePasse & ";" & "DRIVER={SQL Server};Server=" & NomServeur & ";Database=" & NomBaseDeDonnées & ";"
    cnx.ConnectionString = "Driver={SQL Server};Server=[InfoCachée];Database=[InfoCachée];Trusted_Connection=yes;"
    'Ouverture de la base de données
    cnx.Open
    'Exécution de la requête
    rst.Open [InfoCachée], cnx
    rst.Close
    Set rst = Nothing
    Set request = Nothing
    Set cnx = Nothing

thanks again for your comment