Form to Import Inventory to SQL table NOT linked in Access?

[misscrf]

I have a form that tracks hard drives. Their locations, and information about them (serial, etc). I am working on a form button that will do a few things. First it checks the current project a user is in, and then takes the project key and checks a SQL database for existence of a table with that name. If it doesn't exist, I want to create that table. Once the table exists, I want to prompt the user for a file (it is a text file) and insert those records to that SQL table.

Here is the thing. The Access database that exists has linked tables to a SQL database. The database for the inventories is another one. I won't have those tables linked into the Access front end. There is no point to linking them, as far as I can see, except that I can't see to insert the data from the text files to a SQL table that isn't linked in the access FE. Does anyone have any experience with doing this?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Andrzejek]

Are you saying every user's 'project' has its own table in SQL Server?
Looks like very bad data base design... :-(

Can you combine all those 'project' tables into just one table?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[misscrf]

This is actually intentional. The database structure we have, for this application, is to house the project drives, and what happens to them. Uploading an inventory of a 2 TB drive will be massive. We need to keep projects separate, and this is the best way to do that. The inventories are meant to just allow us to validate what we have on each drive in a project, but theses tables won't be a big part of the application, other than that. Normally I would agree with you, but this concept has been put together by a few developers, all of whom are smarter than I am.

There is a specific purpose for this. The main application database is normalized and has a good structure. We just don't want to bog down that database with 1 table or more, full of inventory records that would just be way too voluminous. This will help us to segregate inventories for each project, access them if need be, but keep them out of the way of the main application's purpose and functions.

If anyone can assist with how to accomplish this, I would be most appreciative. I have started the pieces of working on this, but having some issues. Below is the code I have for on-click of a button. It checks for the table to exist. If it doesn't it is supposed to create it. I am having trouble being able to create the SQL table, which would be a remote call from an Access application to this SQL server database using VBA to create the table. I would think once I can get that working, writing an insert statement to insert the records won't be that bad.

If InvTblExists(Me.projectkey, "File") = False Then
    CreateFileList (Me.projectkey)
'   MsgBox "There is no File Table"
End If

Public Function InvTblExists(PKey As String, FType As String)
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim csql As String
Dim strTempID As String
Dim tableToFind As String

tableToFind = PKey & FType & "List"
con.ConnectionString = "Provider=SQLNCLI11;" _
         & "Server=lit-sql;" _
         & "Database=123DriveInventories;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"
con.Open

Set cmd.ActiveConnection = con
cmd.CommandText = "SELECT COUNT(*) FROM sys.objects WHERE Type = 1 AND [Name]='" & tableToFind & "'"
Set rst = cmd.Execute
strTempID = rst.Fields(0).Value

'MsgBox strTempID & " Is the return value!"
If strTempID = 0 Then
    strExists = "False"
Else
    strExists = "True"
End If

con.Close
Set con = Nothing
End Function

Public Function CreateFileList(PKey As String)
Dim con As Connection
Dim cmd As Command
Dim rst As Recordset
Dim csql As String
Dim strTempID As String

'con.ConnectionString = "Provider=SQLNCLI11;" _
'         & "Server=MySQLServerABC001;" _
'         & "Database=123DriveInventories;" _
'         & "Integrated Security=SSPI;" _
'         & "DataTypeCompatibility=80;" _
'         & "MARS Connection=True;"
'con.Open
'
'Set cmd.ActiveConnection = con
'cmd.CommandText = "CREATE TABLE [123DriveInventories].[dbo].[" & PKey & "FileList]([id] [int] IDENTITY(1,1) NOT NULL, " & _
'    "[FKDrive] [int] NULL, " & _
'    "[FileName] [nvarchar](255) NULL, " & _
'    "[Extension] [nvarchar](255) NULL, " & _
'    "[FileSize] [bigint] NULL, " & _
'    "[ModifiedDate] [datetime] NULL, " & _
'    "[FullPathName] [nvarchar](4000) NULL, " & _
'    "[FullPathMAX] [nvarchar](max) NULL, " & _
'    "[createdate] [datetime] NULL, " & _
'    "[modifydate] [datetime] NULL, " & _
'    "[createuser] [nvarchar](50) NULL, " & _
'"CONSTRAINT [PK_" & PKey & "FileList] 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] " & _
'"GO SET ANSI_PADDING OFF " & _
'"GO ALTER TABLE [123DriveInventories].[dbo].[" & PKey & "FileList] ADD  CONSTRAINT [DF_" & PKey & "FileList_createdate]  DEFAULT (getdate()) FOR [createdate] " & _
'"GO ALTER TABLE [123DriveInventories].[dbo].[" & PKey & "FileList] ADD  CONSTRAINT [DF_" & PKey & "FileList_modifydate]  DEFAULT (getdate()) FOR [modifydate] " & _
'"GO CREATE TRIGGER [123DriveInventories].[dbo].[" & PKey & "FileList_MODS] ON [dbo].[" & PKey & "FileList] AFTER UPDATE AS SET NOCOUNT ON " & _
'"GO UPDATE [123DriveInventories].[dbo].[" & PKey & "FileList] SET " & PKey & "FileList.[Modified] = getdate(), " & PKey & "FileList.[ModifiedBy] = User FROM DELETED d WHERE " & PKey & "FileList.Id = d.Id"
'
'Set rst = cmd.Execute
'strTempID = rst.Fields(0).Value

If InvTblExists(PKey, "File") = False Then
    MsgBox "Failed to create " & PKey & "FileList table!! Oh No!"
End If

con.Close
Set con = Nothing

End Function

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Andrzejek]

In Oracle you can create an empty copy of a table with statement like this:

create table xyz_new as select * from xyz where 1=0

I am sure SQL Server would have something like that as well.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[misscrf]

I don't have oracle, and this challenge is a bit more complicated than a sql create statement. Can anyone who might have experience opening a connection to a sql server and creating a table to a remote sql database, please let me know if you have suggestions for this?

Thank you.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

"FileList.[Modified] = getdate(), " & PKey & "FileList.[ModifiedBy] = User

You don't have Modified and ModifiedBy columns in your table.
There are potentially other errors. I would assign the entire SQL string to a variable first and then execute it.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks, Duane. I will check that. Even before that, I can't seem to get the connection to work. One thought I had was to make the create table statement into a stored procedure, and execute that from the vba. Have you had success doing that before?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

misscrf,
How many tasks is your SQL performing? I would start with one simple tasks to see if it works. Only after the first works can you add more complexity.

This worked for me after changing the provider as well as my server and database. Note I commented out some of the "tasks".

Public Function CreateFileList(PKey As String)
    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim csql As String
    Dim strTempID As String
    Set con = New ADODB.Connection
    con.ConnectionString = "Provider=SQLNCLI10;" _
            & "Server=MyLocalSQLServer;" _
             & "Database=MyTestDatabase;" _
             & "Trusted_Connection=yes;"
    con.Open
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    csql = "CREATE TABLE [test].[dbo].[" & PKey & "FileList]([id] [int] IDENTITY(1,1) NOT NULL, " & _
        "[FKDrive] [int] NULL, " & _
        "[FileName] [nvarchar](255) NULL, " & _
        "[Extension] [nvarchar](255) NULL, " & _
        "[FileSize] [bigint] NULL, " & _
        "[ModifiedDate] [datetime] NULL, " & _
        "[FullPathName] [nvarchar](4000) NULL, " & _
        "[FullPathMAX] [nvarchar](max) NULL, " & _
        "[createdate] [datetime] NULL, " & _
        "[modifydate] [datetime] NULL, " & _
        "[createuser] [nvarchar](50) NULL, " & _
    "CONSTRAINT [PK_" & PKey & "FileList] 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]; " [COLOR=#4E9A06]' " & _[/color][COLOR=#4E9A06] 
    'vbCrLf & "  " & vbCrLf & _
    'vbCrLf & "  " & vbCrLf & " ALTER TABLE [test].[dbo].[" & PKey & "FileList] ADD  CONSTRAINT [DF_" & PKey & "FileList_createdate]  DEFAULT (getdate()) FOR [createdate]; " & _
    'vbCrLf & "  " & vbCrLf & " ALTER TABLE [test].[dbo].[" & PKey & "FileList] ADD  CONSTRAINT [DF_" & PKey & "FileList_modifydate]  DEFAULT (getdate()) FOR [modifydate] ;" & _
    'vbCrLf & " GO " & vbCrLf & " CREATE TRIGGER [" & PKey & "FileList_MODS] ON [" & PKey & "FileList] AFTER UPDATE AS SET NOCOUNT ON " & _
    'vbCrLf & " UPDATE [" & PKey & "FileList] SET " & PKey & "FileList.[Modifydate] = getdate(), " & PKey & "FileList.[createuser] = User FROM DELETED d WHERE " & PKey & "FileList.Id = d.Id;"[/color]    Debug.Print csql
    cmd.CommandText = csql

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks, Duane. I ended up making the create table into a stored proc on the server. I tested that and it works correct on the server. Now I am trying to either use VBA or a pass-through query to execute it with the parameter for the table name, so I can run it on-click of the form button in the access app.

I've been researching that, but so far I haven't found a method that will work. Still at it....

I will post back if I get one working!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You can modify the SQL property of your pass-through to update the table name.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Got it!!! So here is my solution.

I have the stored proc on the server. It requires a parameter for the table name of the table that the stored proc is creating.

On a form, I have a button, which on click has the following code:

Private Sub cmdImportInventory_Click()
Dim varfile As Variant
Dim f As Object
Dim strTemp As String
Dim foSQL As String
Dim fiSQL As String

'IF FILE AND FOLDER TABLES DOESN'T EXIST - CREATE THEM FOR PROJECT GROUP
If InvTblExists(Me.bprojectkey, "Folder") = False Then
    CreateFolderList (Me.bprojectkey)
'   MsgBox "There is no Folder Table"
End If

End Sub

This is calling a public function, called InvTblExists , which is a function to first check if the table I want to create, already exists. If it doesn't, I run another public function to create it, while passing a projectkey parameter, which is put into the parameter for the table name.

this is the public function for checking if the table exists. I run it after I create the table, again, to make sure it completed successfully and I can find the table.

Public Function InvTblExists(BKey As String, FType As String)
Dim con As New ADODB.connection
Dim cmd As New ADODB.command
Dim rst As New ADODB.Recordset
Dim csql As String
Dim strTempID As String
Dim tableToFind As String

tableToFind = "tbl" & BKey & FType & "List"
con.connectionString = "Provider=SQLNCLI11;" _
         & "Server=ABCServer001;" _
         & "Database=123Database;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"
con.Open

Set cmd.ActiveConnection = con
cmd.CommandText = "SELECT COUNT(*) FROM sys.objects WHERE [Name] = '" & tableToFind & "'"
Set rst = cmd.Execute
strTempID = rst.Fields(0).Value

'IF YOU NEED TO CHECK THAT IT'S WORKING
'MsgBox strTempID & " Is the return value!"

con.Close
Set con = Nothing

InvTblExists = strTempID

End Function

This is the public function that actually runs the stored proc. It took some tweaking, but it works like a charm!

Public Function CreateFileList(BKey As String)
Dim qdef As DAO.QueryDef
Dim tbl As String
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = "ODBC;DRIVER=SQL Server;SERVER=ABCServer001;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=123Database"
tbl = "tbl" & BKey & "FileList"
qdef.sql = "sp_FileListCreate '" & tbl & "'"
qdef.ODBCTimeout = 0
qdef.ReturnsRecords = False
qdef.Execute

If InvTblExists(BKey, "File") = 0 Then
    MsgBox "Failed to create tbl" & BKey & "FileList table!! Oh No!"
Else
    MsgBox "Successfully Created tbl" & BKey & "FileList table!! Who's your daddy?"
End If

qdef.Close

End Function

The next steps in the code for the on-click event of the command button, will be to ask a user for a text file, which will then be imported into that table. Whew! 1 step at a time. Thanks for all your replies and help. Hopefully this solution will help someone else too!


misscrf

It is never too late to become what you could have been ~ George Eliot