problem with odbc connection or vba code

[kerveros2019]

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]

try googling "ms access SQL create table"

 

[kerveros2019]

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]

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]

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]

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.