Results 1 to 4 of 4
  1. #1
    Fixer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    7

    Question I feel I am missing something here...

    The AssignUserID subroutine below alters a global variable named USERID based on user input.


    Code:
    Public Sub daoCreateSQLTables()    Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strConnect As String
        
        On Error GoTo errblock
        
        'Get UserID
        If USERID = "" Then Call AssignUserID(X)
    GetUserID:
        Select Case StrPtr(USERID)
        Case 0
            A = MsgBox("I am unable to run without a username. Sorry")
            End
        Case Else
            If USERID = "" Then Call AssignUserID(X)
        End Select
        If USERID = "" Then GoTo GetUserID
        
    ' SQL Server connection string
        strConnect = "ODBC;"
        strConnect = strConnect & "DSN=bogus;UID=" & USERID & ";PWD=password;DBQ=BOGUS;"
        strConnect = strConnect & "DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;"
        strConnect = strConnect & "FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;"
        strConnect = strConnect & "DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"
    
    
        Set db = CurrentDb
        Set tdf = db.CreateTableDef("Test")
    
    ' HERE IS WHERE IT BREAKS
        tdf.SourceTableName = "RUDatabase.Test"
    ' IT BREAKS ABOVE
    
    
        tdf.Connect = strConnect
        db.TableDefs.Append tdf
        db.TableDefs.Refresh
        Application.RefreshDatabaseWindow
        Exit Sub
    errblock:
           MsgBox Err.Description
           Exit Sub
    End Sub
    If the original database has a table named "Test" it breaks on the line above, stating that a table named Test is already present, but if I remove that table, it breaks where I have specified.

    What I want to accomplish:

    Run an SQL request to a distant server, requiring authentication (see the second line in srtConnect above, details are concealed).
    Create a new table in Access with the results of the SQL request.

    At this point I am very frustrated. I thought I had learned enough to put this together but it seems I am missing something. This is my first time trying to use DAO, so maybe I am using that wrong. I have this entire process done in Excel, but the number of records is coming pretty close to 1 million rows, which will eventually break Execl, so I need to convert it to Access and VB plays differently in Access than Excel.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Manually attach (link) the outside table
    now that you have it, build a make table query that creates the local table Test.
    delete the linked tbl

    now in your code after you connect, run the make table query.

  3. #3
    Fixer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    7
    Quote Originally Posted by ranman256 View Post
    Manually attach (link) the outside table
    now that you have it, build a make table query that creates the local table Test.
    delete the linked tbl

    now in your code after you connect, run the make table query.
    That outside table is millions of rows and several dozen columns. It takes a LONG time to download in its entirety, which is why I am trying to just run the SQL request to get just the info I need. Also, this process needs to be automatic, not manual. The folks I am doing this for won't know how to do it, but they know how to push a button to get it done.

    As I stated, I can make these SQLs happen in Excel. If I create links to those Excel files and then merge them together into Access, can I then delete the original files? Would that remove the tables or will that data remain?

    (If so, then I just need to figure out the VBA to pull in the tables into Access from Excel.)

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would suggest using
    Set tdf = db.TableDefs("Test")

    Instead of
    Set tdf = db.CreateTableDef("Test")

    For this to work, create a table with the correct fields and do not delete it. I did not notice any code that deletes a table. And I do not see any code that is creating new columns. So, it may be best to just leave the table structure there for your code to use. Afterwards, you can delete the records within the table.

    What you are doing, currently, is creating a table with the name Test and then, with the line of code that breaks, try to rename it.
    tdf.SourceTableName = "RUDatabase.Test"

    SourceTableName is a property of the table. You would use this property to read or edit the name of the table.

    This is an example of some of my code to connect to another Access DB over a network.
    tdf.Properties("Connect") = ";DATABASE=" & strPath

    Take a look at this link for the correct Specifier for your Database type
    https://msdn.microsoft.com/EN-US/lib.../ff193791.aspx

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

Similar Threads

  1. What am I missing???
    By tyewonk in forum Access
    Replies: 4
    Last Post: 10-14-2013, 12:07 PM
  2. Custom look-and-feel to Access Forms and Reports
    By LindaRuble in forum Programming
    Replies: 1
    Last Post: 01-03-2013, 04:37 PM
  3. Replies: 4
    Last Post: 08-22-2012, 11:49 AM
  4. ok what am i missing here ?
    By baseborn in forum Forms
    Replies: 6
    Last Post: 12-21-2010, 01:46 PM
  5. Replies: 3
    Last Post: 02-09-2010, 03:50 PM

Tags for this Thread

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