Connection and Create New Table

[Guest]

I have a database that is split, one holds my tables while the other consists
of all my forms, modules etc. I create a new table once a month to hold that
month's data. I have to do this due to huge volume of data. Problem is that
my code isn't working and am not sure why? Can someone please have a look.
Also, I'd like to know how to link the table afterwards back into my forms
database.

One more thing, I have an LMS database with a primary key called polisnommer
and for some or other weird reason I also get a message saying that the
primary key for LMS doesn't exist when I try to make a foreign key to that
polisnommer in my new table.

'set connection to database
Dim cnn1 As New ADODB.Connection
Dim cmd1 As ADODB.Command
Dim strTemp As String
Dim TablePath As String

TablePath = Form_DefaultPage.tbLinkTable.Value
Set cnn1 = New ADODB.Connection



cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =" & TablePath & ";"

Set cmd1 = New ADODB.Command


cmd1.ActiveConnection = cnn1
cmd1.CommandType = adCmdText
cmd1.CommandText = "Create Table " & Form_Elektronies.MonthString & "(
" & _
"Polisnommer VarChar(255) Null Unique," & _
"BROKERCODE VarChar(255) Null," & _
"NETTOPREMIE Double Null," & _
"KOMMISSIE VarChar(255) Null," & _
"KOMMISSIETotAAL Double Null," & _
"KOMMISSIESTAATDATUM VarChar(255) Null," & _
"VERWYSING VarChar(255) Null," & _
"Date Date/Time Null," & _
"FOREIGN KEY (Polisnommer) References LMS)"
cmd1.Execute


cnn1.Close
Set cnn1 = Nothing

Please, need help on this. Thanks

 

[pietlinden]

I have a database that is split, one holds my tables while the other consists
of all my forms, modules etc. I create a new table once a month to hold that
month's data. I have to do this due to huge volume of data. Problem is that
my code isn't working and am not sure why? Can someone please have a look.
Also, I'd like to know how to link the table afterwards back into my forms
database.

One more thing, I have an LMS database with a primary key called polisnommer
and for some or other weird reason I also get a message saying that the
primary key for LMS doesn't exist when I try to make a foreign key to that
polisnommer in my new table.

'set connection to database
Dim cnn1 As New ADODB.Connection
Dim cmd1 As ADODB.Command
Dim strTemp As String
Dim TablePath As String

TablePath = Form_DefaultPage.tbLinkTable.Value
Set cnn1 = New ADODB.Connection

cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =" & TablePath & ";"

Set cmd1 = New ADODB.Command

cmd1.ActiveConnection = cnn1
cmd1.CommandType = adCmdText
cmd1.CommandText = "Create Table " & Form_Elektronies.MonthString & "(
" & _
"Polisnommer VarChar(255) Null Unique," & _
"BROKERCODE VarChar(255) Null," & _
"NETTOPREMIE Double Null," & _
"KOMMISSIE VarChar(255) Null," & _
"KOMMISSIETotAAL Double Null," & _
"KOMMISSIESTAATDATUM VarChar(255) Null," & _
"VERWYSING VarChar(255) Null," & _
"Date Date/Time Null," & _
"FOREIGN KEY (Polisnommer) References LMS)"
cmd1.Execute

cnn1.Close
Set cnn1 = Nothing

Please, need help on this. Thanks

Why are you creating a new table at all? You can just continue
writing the records to the same table and then using a query to get
the partition/set you want.

 

[Guest]

Hi

Problem is that each month alone hold around 14000 to 15000 rows of data.
It's better to create a montly table for each. Also quicker to find data etc.