Results 1 to 7 of 7
  1. #1
    Leopolleke is offline Novice
    Windows XP Access 2003
    Join Date
    May 2018
    Posts
    4

    Right syntax to drop a table.....

    Hi all,

    sorry for this probably very basic question, but I can't find it......
    I have an access (2003) database and tables on our webserver (remote) on MySQL server.
    I would like to import a table in VBA from MySQL to Access (this works), work with the data and export the table again from Access to MySQL.
    I know linking the tables would be the solution, but for some reason it takes forever to work on the remote table....
    Importing is OK (delete the table in Access and import it from MySQL)
    While exporting, I run into a problem that MySQL replies that the table already exists (which is true
    I can't find the right syntax in VBA to drop the table on the remote server......

    Help?

    Thanks in advance,



    Leo

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    What about a delete query ,followed with an append query?

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Leo,
    I have some code on http://forestbyte.com/vba-code-samples/ to do that using a pass-through query (the last sample). I will post the entire module in here as well:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Function vcAlterTable(strSourceTable As String, sAlterSQL As String, sUseConnectionStringFrom As String)
    Dim db As DAO.Database
    Dim qdExtData As QueryDef
    Dim strSQL As String
    
    
    On Error Resume Next
    
    
    Set db = CurrentDb
    If ObjectExists("QUERY", "sqlALTER_TABLE") Then DoCmd.DeleteObject acQuery, "sqlALTER_TABLE"
    strSQL = sAlterSQL
    Set qdExtData = db.CreateQueryDef("sqlALTER_TABLE")
    
    
    qdExtData.Connect = CurrentDb.TableDefs(sUseConnectionStringFrom).Connect
    qdExtData.ReturnsRecords = False
    qdExtData.SQL = strSQL
    
    
    CurrentDb.QueryDefs("sqlALTER_TABLE").Execute
    
    
    'lets refresh the link
    CurrentDb.TableDefs(strSourceTable).RefreshLink
    
    
    qdExtData.Close
    db.Close
    Set db = Nothing
    MsgBox "ALTER TABLE STATEMENT COMPLETED - PLEASE CHECK THE TABLE IN DESIGN VIEW"
    End Function
    
    
    ‘this function might already be present
    Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
    ' Pass the Object type: Table, Query, Form, Report, Macro, or Module
    ' Pass the Object Name
         
         Dim db As Database
         Dim tbl As TableDef
         Dim qry As QueryDef
         Dim i As Integer
         
         Set db = CurrentDb()
         ObjectExists = False
         
         If strObjectType = "Table" Then
              For Each tbl In db.TableDefs
                   If tbl.Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next tbl
         ElseIf strObjectType = "Query" Then
              For Each qry In db.QueryDefs
                   If qry.Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next qry
         ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
              For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
                   If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next i
         ElseIf strObjectType = "Macro" Then
              For i = 0 To db.Containers("Scripts").Documents.Count - 1
                   If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next i
         Else
              MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
         End If
         
    End Function
    To use it just call it in your VBA like this Call vcAlterTable ("YourMySQL_Table","DROP TABLE YourMySQL_Table;",CurrentDB.tabledefs("AnyMySQLLin kedTable").Connect)

    Cheers,
    Vlad

  4. #4
    Leopolleke is offline Novice
    Windows XP Access 2003
    Join Date
    May 2018
    Posts
    4
    Hi Ranman & Gicu,
    thanks for the replies. I'm going to look into these solutions.
    Thank you,
    Leo

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    I haven't worked with MySQL, so I aren't sure how much is following example (MS SQL Server) usable, but maybe you find something here.

    On SQL Server, a linked server MyLinkedServer for MS Access database is defined;
    In SQL Server DB MyDatabase, a stored procedure is defined, which runs script like
    Code:
    USE MyDatabase
    
    TRUNCATE TABLE MySqlTable
    INSERT INTO MySqlTable
    SELECT * FROM MyLinkedServer...MyAccessTable
    A Job is scheduled to run the Stored Procedure

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    you can drop a SQL table and recreate it from a query with the following SQL syntax:

    Code:
     
    --drop existing table
     
    
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[SchemaName].[TableName]') AND type in (N'U'))
     DROP TABLE [SchemaName].[TableName]
    
        
    --recreate table from query
    Select *into [SchemaName].[TableName] from [SchemaName2].[TableName2]
    
     
    

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    More variations on the same theme...

    Code:
    /****** Object:  Table [dbo].[tblSysObjectTypes]    Script Date: 30/04/2017 23:51:13 ******/
    --remove field if it exists
    IF EXISTS (
      SELECT * 
      FROM   sys.columns 
      WHERE  object_id = OBJECT_ID(N'[dbo].[tblSysObjectTypes]') 
             AND name = 'System'
    )
    
    
        BEGIN
            ALTER TABLE [dbo].[tblSysObjectTypes] DROP COLUMN [System] 
        END
    GO
    Code:
    /****** Object:  Table [dbo].[tblDatabaseObjects]    Script Date: 09/04/2017 11:01:26******/
    --delete table if it exists
    IF EXISTS (
        SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'[dbo].[tblDatabaseObjects]') AND type in (N'U')
    )
        BEGIN
            DROP TABLE [dbo].[tblDatabaseObjects]
        END
    GO

    Code:
    /****** Object:  Table [dbo].[tblSysObjectTypes]    Script Date: 30/04/2017 23:51:13 ******/
    --add new field it it doesn't exist
    IF NOT EXISTS (
      SELECT * 
      FROM   sys.columns 
      WHERE  object_id = OBJECT_ID(N'[dbo].[tblSysObjectTypes]') 
             AND name = 'System'
    )
    
        BEGIN
            ALTER TABLE [dbo].[tblSysObjectTypes] ADD [System] [bit] NOT NULL CONSTRAINT [DF_tblSysObjectTypes_System]  DEFAULT ((0))
        END
    GO

    Code:
    /****** Object:  Table [dbo].[tblDatabaseSQLErrors]    Script Date: 09/04/2017 00:03:20 ******/
    --create new table if it doesn't exist
    IF NOT EXISTS (
        SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'[dbo].[tblDatabaseSQLErrors]') AND type in (N'U')
    )
    
    
        BEGIN
            SET ANSI_NULLS ON
    
    
            SET QUOTED_IDENTIFIER ON
    
    
            SET ANSI_PADDING ON
    
    
            CREATE TABLE [dbo].[tblDatabaseSQLErrors](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [ObjectName] [varchar](255) NULL,
                [ObjectType] [varchar](20) NULL,
                [ErrorDateTime] [datetime] NULL,
                [ErrorNumber] [int] NULL,
                [ErrorDescription] [text] NULL,
                [Fixed] [bit] NOT NULL CONSTRAINT [DF_tblDatabaseSQLErrors_Fixed]  DEFAULT ((0)),
                [DateFixed] [datetime] NULL,
             CONSTRAINT [PK_tblDatabaseSQLErrors] PRIMARY KEY CLUSTERED 
            (
                [ID] ASC
            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
            ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    
            SET ANSI_PADDING OFF
    
    
        END
    
    
    GO
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Drop Table for each loop
    By gammaman in forum Programming
    Replies: 8
    Last Post: 11-23-2015, 07:19 AM
  2. Replies: 2
    Last Post: 09-12-2014, 08:43 PM
  3. Replies: 3
    Last Post: 10-25-2012, 09:37 PM
  4. Replies: 3
    Last Post: 11-29-2011, 07:01 AM
  5. Table drop down list filter
    By jbataille86 in forum Access
    Replies: 2
    Last Post: 08-05-2011, 08:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums