problem with odbc connection or vba code (1 Viewer)

kerveros2019

New member
Local time
Today, 07:30
Joined
Sep 25, 2019
Messages
4
Hey everyone,


hope you guys can help me.


i have established a dao connection to my localhost (think so) and i am having problem creating a table in that database.


my problem is in this code:


Option Compare Database
Option Explicit

Dim db As Dao.Database
Dim rs As Dao.Recordset
Dim Conn As String


---------


Private Sub cmdTEST_Click()
'Create table and fields using DAO
Dim tdf As Dao.TableDef
Dim fldID As Dao.Field, fldName As Dao.Field

connectToDb

'Create the table definition
Set tdf = db.CreateTableDef("tblMsisdn")

'Create the field definitions
Set fldID = tdf.CreateField("ID", DataTypeEnum.dbLong)
fldID.Attributes = dbAutoIncrField
fldID.Required = False

Set fldName = tdf.CreateField("custName", DataTypeEnum.dbText)
fldName.AllowZeroLength = False
fldName.Required = False

'add the fields to the table
tdf.Fields.Append fldID
tdf.Fields.Append fldName

'add the table to the database
'db.TableDefs.Append tdf

'refresh the table and database
db.TableDefs.Refresh
Application.RefreshDatabaseWindow

Set fldID = Nothing
Set fldName = Nothing
Set tdf = Nothing
Set db = Nothing

Debug.Print "done"
End Sub


--------


Private Sub connectToDb()
Conn = "Driver={MariaDB ODBC 3.1 Driver};Server=localhost;UID=####;PWD=#####;DB=#####;Port=3306" 'Connection String to log into maria db using cretendials
Set db = OpenDatabase("", False, False, Conn) 'opens database from conn string, to use user prompt use "ODBC;" instead of conn

If Not db Is Nothing Then
MsgBox "Connected to zt-dbase"
End If
End Sub


i use debug and when i get to the point



db.TableDefs.Append tdf i get error run-time error 3146 odbc connection failed


Whereas if i do this.... (which is not exactly what i need)



Private Sub cmdExecute_Click()
Dim sql As String

connectToDb

On Error Resume Next
DoCmd.DeleteObject acTable, "ALLDBSTEST"
On Error GoTo 0

sql = "SELECT [dialer lists].FIXED INTO ALLDBSTEST FROM [dialer lists]"

Debug.Print sql

db.Execute sql

MsgBox "Created Table ALLDBSTEST"

db.Close

End Sub


it does create a new table named alldbstest on my server
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:30
Joined
May 7, 2009
Messages
13,796
try googling "ms access SQL create table"
 

kerveros2019

New member
Local time
Today, 07:30
Joined
Sep 25, 2019
Messages
4
i have done that already and i get error 3032


Private Sub cmdCreateTable_Click()
Dim sql As String

'On Error Resume Next
'DoCmd.DeleteObject acTable, "Zita_Db_Test"
'On Error GoTo 0

sql = "CREATE TABLE [Zita_Db_Test] ([LAST NAME] VARCHAR(255), [FIRST NAME] VARCHAR(255), " & _
"[TAX NUMBER] VARCHAR(255), [MSISDN] VARCHAR(255), " & _
"ADDRESS VARCHAR(255), CITY VARCHAR(255),[POSTAL CODE] VARCHAR(255), " & _
"STATUS VARCHAR(255),
[LIST NAME] VARCHAR(255));"

Debug.Print sql

Set db = OpenDatabase("", False, False, "ODBC;") 'opens database from conn string, to use user promt use "ODBC;" instead of conn

db.Execute sql, dbFailOnError

Set db = Nothing

MsgBox "Created Table Zita_Db_Test"

db.Close

End Sub


run-time error 3032
Cannot perform this operation. (Error 3032)
 

kerveros2019

New member
Local time
Today, 07:30
Joined
Sep 25, 2019
Messages
4
for some kind of reason doing an DAO.Database gives me errors.


i solved my problem using an adodb.connection and i managed to create my table.


i used this code:


Private Sub cmdadodb_Click()
Dim db As ADODB.Connection
Set db = New ADODB.Connection

db.ConnectionString = "Driver={MariaDB ODBC 3.1 Driver};Server=localhost;UID=####;PWD=#####;DB=#####;Port=3306"

db.Open

'create table
db.Execute "DROP TABLE IF EXISTS my_ado"
db.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _
& "txt text, dt date, tm time, ts timestamp)"

db.Close

End Sub


anyone can tell me why using dao gives me



odbc call failed error 3146

whereas adodb.connection doesnt?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:30
Joined
May 7, 2009
Messages
13,796
glad you did it.

as far as my small understanding, dao is for access/office only.
while ado is a universal connector to other db.

both have their strength and weaknesses.
 

sonic8

AWF VIP
Local time
Today, 08:30
Joined
Oct 27, 2015
Messages
364
anyone can tell me why using dao gives me

odbc call failed error 3146

whereas adodb.connection doesnt?
ODBC-Direct-Workspaces have been removed from DAO with Acccess 2007 and the corresponding upgade to the ACE-Library for DAO. They would have allowed you to use DAO to do the stuff you try to do. Now you either need to use ADO or a DAO Pass-Through-Query to execute DDL on any remote server.
 

Users who are viewing this thread

Top Bottom