Make Table statement issue

[creyn]

Can someone help with why this Create Table statement is not working correctly? Below is the code I am using. I keep getting a Run-time error '91': Object variable or With block variable not set.

Public Sub Command0_Click()
Dim MyFolder As String
Dim MyFile As String
Dim MyFolder2 As String
Dim DQ As String
Dim MyData As String
Dim myQueryName As String
Dim myExportFileName As String
Dim sysdate As Date
Dim dbs As Database
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Dim fld As Folder
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = ""
.AllowMultiSelect = False
Intialfilename = ""

If .Show = True Then
varfile = .SelectedItems(1)
strFolder = varfile
Else
rtn = SysCmd(4, "Action aborted by user")
Exit Sub
End If

DoCmd.SetWarnings False
dbs.Execute "Create Table Import_Data([Field1] Text(100) NOT NULL, [FileName] Text(100) NOT NULL, Date Date/Time);"
MyFolder = varfile
MyFile = Dir(MyFolder)
Call fso.CopyFile(MyFolder, MyFile & ".txt")
MyFile = Dir(MyFolder & ".txt")
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from Import_Data"
DoCmd.TransferText acLinkDelim, "Import_Data Specification", "Import_Data", MyFile, True
DoCmd.RunSQL "UPDATE Import_Data SET FileName = '" & MyFile & "', [Date] = '" & Date & "' WHERE FileName IS NULL And Date is NULL"
DoCmd.RunSQL "INSERT INTO SpecialCharacter_Errors ( Field1, FileName, [Date] ) SELECT Special_Characters.Field1, Special_Characters.FileName, Special_Characters.Date " & _
"FROM Special_Characters;"
DoCmd.OpenQuery "Qry_Import_Data"
DoCmd.Close acQuery, "Qry_Import_Data"
DoCmd.OpenQuery "Column_Export"
DoCmd.Close acQuery, "Column_Export"
DoCmd.TransferText acExportDelim, "Export_Data Specification", "Column_Export", [MyFile], False
DoCmd.SetWarnings False
MsgBox "Process Completed"
End With
Set fso = Nothing
dbs.Execute "Drop TABLE Import_Data;"
dbs.Close
End Sub

 

[ranman256]

if would use queries, instead of sql, the query will tell you what is wrong.
there's no need to write all this code.

Can someone help with why this Create Table statement is not working correctly? Below is the code I am using. I keep getting a Run-time error '91': Object variable or With block variable not set.

Public Sub Command0_Click()
Dim MyFolder As String
Dim MyFile As String
Dim MyFolder2 As String
Dim DQ As String
Dim MyData As String
Dim myQueryName As String
Dim myExportFileName As String
Dim sysdate As Date
Dim dbs As Database
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Dim fld As Folder
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = ""
.AllowMultiSelect = False
Intialfilename = ""

If .Show = True Then
varfile = .SelectedItems(1)
strFolder = varfile
Else
rtn = SysCmd(4, "Action aborted by user")
Exit Sub
End If

DoCmd.SetWarnings False
dbs.Execute "Create Table Import_Data([Field1] Text(100) NOT NULL, [FileName] Text(100) NOT NULL, Date Date/Time);"
MyFolder = varfile
MyFile = Dir(MyFolder)
Call fso.CopyFile(MyFolder, MyFile & ".txt")
MyFile = Dir(MyFolder & ".txt")
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from Import_Data"
DoCmd.TransferText acLinkDelim, "Import_Data Specification", "Import_Data", MyFile, True
DoCmd.RunSQL "UPDATE Import_Data SET FileName = '" & MyFile & "', [Date] = '" & Date & "' WHERE FileName IS NULL And Date is NULL"
DoCmd.RunSQL "INSERT INTO SpecialCharacter_Errors ( Field1, FileName, [Date] ) SELECT Special_Characters.Field1, Special_Characters.FileName, Special_Characters.Date " & _
"FROM Special_Characters;"
DoCmd.OpenQuery "Qry_Import_Data"
DoCmd.Close acQuery, "Qry_Import_Data"
DoCmd.OpenQuery "Column_Export"
DoCmd.Close acQuery, "Column_Export"
DoCmd.TransferText acExportDelim, "Export_Data Specification", "Column_Export", [MyFile], False
DoCmd.SetWarnings False
MsgBox "Process Completed"
End With
Set fso = Nothing
dbs.Execute "Drop TABLE Import_Data;"
dbs.Close
End Sub

 

[Norie]

You aren't setting dbs to refer to, well, anything.

 

[creyn]

Thanks