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.