"INSERT INTO" doesn't work

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have a front-end database that just has forms and a back-end database with
the data. I have to programmatically create a new table in the back-end
database, link that new table to my front-end and then insert an initial
value into the table. I can create the table and link the table but I can't
use the following code to insert the initial value.

Dim strSQL As String
Dim strVersion As String


THIS CODE WORKS AND CREATES THE EXTERNAL TABLE
'Create the new VersionInfoTable and link it
Call CreateVersionInfoTable("C:\Program
Files\InvestigatorNerd\InvestigatorNerd_be.mdb", "VersionInfo")

THIS CODE WORKS AND LINKS THE TABLE TO THE FRONT-END
Call CreateLinkedAccessTable("C:\Program
Files\InvestigatorNerd\InvestigatorNerd.mdb", _
"C:\Program Files\InvestigatorNerd\InvestigatorNerd_be.mdb",
"VersionInfo", "VersionInfo")




THIS CODE BELOW IS NOT WORKING AND NOT PRODUCING AN ERROR MESSAGE

'update the version number
strVersion = "Beta"

strSQL = "INSERT INTO VersionInfo(Version) " & _
"VALUES( """ & strVersion & """)"

DoCmd.Close acForm, Me.Name

ANY SUGGESTIONS WILL BE GREATLY APPRECIATED.

Karen
 
Karen said:
I have a front-end database that just has forms and a back-end
database with the data. I have to programmatically create a new
table in the back-end database, link that new table to my front-end
and then insert an initial value into the table. I can create the
table and link the table but I can't use the following code to insert
the initial value.

Dim strSQL As String
Dim strVersion As String


THIS CODE WORKS AND CREATES THE EXTERNAL TABLE
'Create the new VersionInfoTable and link it
Call CreateVersionInfoTable("C:\Program
Files\InvestigatorNerd\InvestigatorNerd_be.mdb", "VersionInfo")

THIS CODE WORKS AND LINKS THE TABLE TO THE FRONT-END
Call CreateLinkedAccessTable("C:\Program
Files\InvestigatorNerd\InvestigatorNerd.mdb", _
"C:\Program Files\InvestigatorNerd\InvestigatorNerd_be.mdb",
"VersionInfo", "VersionInfo")




THIS CODE BELOW IS NOT WORKING AND NOT PRODUCING AN ERROR MESSAGE

'update the version number
strVersion = "Beta"

strSQL = "INSERT INTO VersionInfo(Version) " & _
"VALUES( """ & strVersion & """)"

DoCmd.Close acForm, Me.Name

ANY SUGGESTIONS WILL BE GREATLY APPRECIATED.

Karen
Click to expand...

The code is doing nothing because -- unless you left something out of
your post -- you never execute the SQL statement. Try inserting this
statement between your assignment to strSQL and the call to DoCmd.Close:

CurrentDb.Execute strSQL, dbFailOnError

Note: if you don't have a reference set to the DAO Object Library, the
dbFailOnError constant won't be defined. We can deal with that if that
turns out to be the case.
 
Hi Dirk,

Well, a product of trying too many things. I put the suggested code in and
it works IF I set a breakpoint at 'strVersion' and step through from there.
If I just try to run the code without breakpoints (i.e. what I want for
'normal' operation); it gets to the 'CurrentDb.Execute strSQL,
dbFailOnError' line and errors out that the table 'Version Info' doesn't
exist. I'm getting terribly discouraged.
 
Karen said:
Hi Dirk,

Well, a product of trying too many things. I put the suggested code
in and it works IF I set a breakpoint at 'strVersion' and step
through from there. If I just try to run the code without breakpoints
(i.e. what I want for 'normal' operation); it gets to the
'CurrentDb.Execute strSQL, dbFailOnError' line and errors out that
the table 'Version Info' doesn't exist. I'm getting terribly
discouraged.
Click to expand...

Hmm. Plainly it's a latency or timing problem of some sort. You don't
show your code for the function "CreateLinkedAccessTable", so I can't
address any possible quirks there, but try this instead of what I
originally posted:

With CurrentDb.
.TableDefs.Refresh
.Execute strSQL, dbFailOnError
End With
 
Yes, it really is a latency problem. I tried your code, it failed on
'.Execute strSQL, dbFailOnError' line and said the 'VersionInfo' table did
not exist. I dragged the cursor back up to 'With CurrentDB" and, second
time through, there were no errors. I don't think the CreateLink code is
the culprit because, later in this whole process I easily build and link two
other tables but they don't need any initial data. Here is the CreateLink
code, just in case.

Sub CreateLinkedAccessTable(strDBLinkFrom As String, _
strDBLinkTo As String, _
strLinkTbl As String, _
strLinkTblAs As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog
' Open a Catalog on the database in which to create the link.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDBLinkFrom

Set tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog
' property to the open Catalog to allow access
' to the Properties collection.
.Name = strLinkTblAs
Set .ParentCatalog = catDB

' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = strDBLinkTo
.Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
End With

' Append the table to the Tables collection.
catDB.Tables.Append tblLink

Set catDB = Nothing
 
Karen said:
Yes, it really is a latency problem. I tried your code, it failed on
'.Execute strSQL, dbFailOnError' line and said the 'VersionInfo'
table did not exist. I dragged the cursor back up to 'With
CurrentDB" and, second time through, there were no errors. I don't
think the CreateLink code is the culprit because, later in this whole
process I easily build and link two other tables but they don't need
any initial data. Here is the CreateLink code, just in case.

Sub CreateLinkedAccessTable(strDBLinkFrom As String, _
strDBLinkTo As String, _
strLinkTbl As String, _
strLinkTblAs As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog
' Open a Catalog on the database in which to create the link.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDBLinkFrom

Set tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog
' property to the open Catalog to allow access
' to the Properties collection.
.Name = strLinkTblAs
Set .ParentCatalog = catDB

' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = strDBLinkTo
.Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
End With

' Append the table to the Tables collection.
catDB.Tables.Append tblLink

Set catDB = Nothing
Click to expand...

I see you're you're using ADOX with a completely independent connection
to the database. It doesn't surprise me in the least that you're having
latency problems. But am I right in thinking that you are creating this
linked table in the current database, which is a Jet database (i.e., an
..mdb file)? If so, you could solve your problem by using the current
database's connection to do the work; bu further, why use ADOX at all?
Why not just:

'----- start of suggested code -----
Sub CreateLinkedAccessTable(strDBLinkTo As String, _
strLinkTbl As String, _
strLinkTblAs As String)

DoCmd.TransferDatabase acLink, _
"Microsoft Access", strDBLinkTo, acTable, strLinkTbl,
strLinkTblAs

End Sub
'----- end of suggested code -----

Note that I've dropped one of the arguments from the procedure, because
I'm assuming you will always be creating the linked table in the current
database.
 
Yes, it really is a latency problem. I tried your code, it failed on
'.Execute strSQL, dbFailOnError' line and said the 'VersionInfo' table did
not exist. I dragged the cursor back up to 'With CurrentDB" and, second
time through, there were no errors. I don't think the CreateLink code is
the culprit because, later in this whole process I easily build and link two
other tables but they don't need any initial data. Here is the CreateLink
code, just in case.

Sub CreateLinkedAccessTable(strDBLinkFrom As String, _
strDBLinkTo As String, _
strLinkTbl As String, _
strLinkTblAs As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog
' Open a Catalog on the database in which to create the link.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDBLinkFrom

Set tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog
' property to the open Catalog to allow access
' to the Properties collection.
.Name = strLinkTblAs
Set .ParentCatalog = catDB

' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = strDBLinkTo
.Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
End With

' Append the table to the Tables collection.
catDB.Tables.Append tblLink

Set catDB = Nothing
Click to expand...



Quite sure that all you need to do is to refresh tabledefs in the
front end.

Richard





Its a good job Einstein never had to fill out one of these Government Grant applications or we never would have found out what e equalled. The West Wing
 
Dirk,

I'm more than willing to try that code but I need to understand some things.
I started this whole horrible evolution just trying to find code to create a
table in a back-end database and ADOX was the code that presented itself
most fully on the Microsoft site.

If you have a simpler piece of code for linking a table, would you also have
a simpler piece of code for creating the table in the backend database?
 
Karen said:
Dirk,

I'm more than willing to try that code but I need to understand some
things. I started this whole horrible evolution just trying to find
code to create a table in a back-end database and ADOX was the code
that presented itself most fully on the Microsoft site.
Click to expand...

If you have a simpler piece of code for linking a table, would you
also have a simpler piece of code for creating the table in the
backend database?
Click to expand...

I dunno; I haven't seen the code you're using for that. If the
back-end database is a Jet database, then probably.
 
OK, this is the ADOX code I used to create the table in the first place:

-----------------------------------------------------
Sub CreateVersionInfoTable(strDBPath As String, strNewTableName As String)
Dim catDBBackend As ADOX.Catalog
Dim catDBFrontend As ADOX.Catalog
Dim col As New ADOX.Column
Dim tblNew As ADOX.Table
Dim strVersion As String
Dim strSQL As String

Set catDBBackend = New ADOX.Catalog
Set catDBFrontend = New ADOX.Catalog

' Open the catalog.
catDBBackend.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDBPath
With col
.Name = "VersionInfoID"
.Type = adInteger
Set .ParentCatalog = catDBBackend
.Properties("AutoIncrement") = True
.Properties("Seed") = CLng(1)
.Properties("Increment") = CLng(1)
End With

Set tblNew = New ADOX.Table
' Create a new Table object.
With tblNew
.Name = strNewTableName
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns
.Append col
.Append "Version", adVarWChar
End With
.Keys.Append "Primary Key", adKeyPrimary, "VersionInfoID"
End With

' Add the new Table to the Tables collection of the database.
catDBBackend.Tables.Append tblNew

Set catDBBackend = Nothing

End Sub
 
Quite wrong in this case, I'm afraid. :-)
Click to expand...

Sorry, meant to reply to this message - was going on the impression
that you were using DAO.

Hmm. Plainly it's a latency or timing problem of some sort. You
don't
show your code for the function "CreateLinkedAccessTable", so I can't
address any possible quirks there, but try this instead of what I
originally posted:

With CurrentDb.
.TableDefs.Refresh
.Execute strSQL, dbFailOnError
End With



Richard




Its a good job Einstein never had to fill out one of these Government Grant applications or we never would have found out what e equalled. The West Wing
 
Karen said:
OK, this is the ADOX code I used to create the table in the first
place:

-----------------------------------------------------
Sub CreateVersionInfoTable(strDBPath As String, strNewTableName As
String) Dim catDBBackend As ADOX.Catalog
Dim catDBFrontend As ADOX.Catalog
Dim col As New ADOX.Column
Dim tblNew As ADOX.Table
Dim strVersion As String
Dim strSQL As String

Set catDBBackend = New ADOX.Catalog
Set catDBFrontend = New ADOX.Catalog

' Open the catalog.
catDBBackend.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=" &
strDBPath With col
.Name = "VersionInfoID"
.Type = adInteger
Set .ParentCatalog = catDBBackend
.Properties("AutoIncrement") = True
.Properties("Seed") = CLng(1)
.Properties("Increment") = CLng(1)
End With

Set tblNew = New ADOX.Table
' Create a new Table object.
With tblNew
.Name = strNewTableName
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns
.Append col
.Append "Version", adVarWChar
End With
.Keys.Append "Primary Key", adKeyPrimary, "VersionInfoID"
End With

' Add the new Table to the Tables collection of the database.
catDBBackend.Tables.Append tblNew

Set catDBBackend = Nothing

End Sub
Click to expand...

Doing the same thing with DAO objects would not be significantly simpler
(see below), but using either DAO or ADO you can create the table using
Data-Definition SQL. Compare the two procedures below:

'------ start of "complicated" DAO code ------
Sub CreateVersionInfoTableDAO_Objects(strDBPath As String,
strNewTableName As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ix As DAO.Index

Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath)
Set tdf = db.CreateTableDef(strNewTableName)

With tdf
Set fld = .CreateField("VersionInfoID", dbLong)
fld.Attributes = fld.Attributes Or dbAutoIncrField
.Fields.Append fld
Set fld = Nothing
.Fields.Append .CreateField("Version", dbText)

Set ix = .CreateIndex("PrimaryKey")
With ix
.Fields.Append .CreateField("VersionInfoID")
.Primary = True
End With

.Indexes.Append ix

End With

db.TableDefs.Append tdf

Set ix = Nothing
Set tdf = Nothing
db.Close

Set db = Nothing

End Sub
'------ end of "complicated" DAO code ------

'------ start of "simple" DAO code ------
Sub CreateVersionInfoTableSQL(strDBPath As String, strNewTableName As
String)

Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath)

strSQL = _
"CREATE TABLE [" & strNewTableName & "] (" & _
"VersionInfoID COUNTER NOT NULL " & _
"CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Version CHAR" & ")"

db.Execute strSQL, dbFailOnError

db.Close

Set db = Nothing

End Sub
'------ end of "simple" DAO code ------
 
Dirk,

OK, I'll print and really study this. Check back tomorrow and I'll be sure
to post what I hope is the solution for me with this info to work from.
Thanks for all the help.

--
Karen
Dirk Goldgar said:
Doing the same thing with DAO objects would not be significantly simpler
(see below), but using either DAO or ADO you can create the table using
Data-Definition SQL. Compare the two procedures below:

'------ start of "complicated" DAO code ------
Sub CreateVersionInfoTableDAO_Objects(strDBPath As String,
strNewTableName As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ix As DAO.Index

Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath)
Set tdf = db.CreateTableDef(strNewTableName)

With tdf
Set fld = .CreateField("VersionInfoID", dbLong)
fld.Attributes = fld.Attributes Or dbAutoIncrField
.Fields.Append fld
Set fld = Nothing
.Fields.Append .CreateField("Version", dbText)

Set ix = .CreateIndex("PrimaryKey")
With ix
.Fields.Append .CreateField("VersionInfoID")
.Primary = True
End With

.Indexes.Append ix

End With

db.TableDefs.Append tdf

Set ix = Nothing
Set tdf = Nothing
db.Close

Set db = Nothing

End Sub
'------ end of "complicated" DAO code ------

'------ start of "simple" DAO code ------
Sub CreateVersionInfoTableSQL(strDBPath As String, strNewTableName As
String)

Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath)

strSQL = _
"CREATE TABLE [" & strNewTableName & "] (" & _
"VersionInfoID COUNTER NOT NULL " & _
"CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Version CHAR" & ")"

db.Execute strSQL, dbFailOnError

db.Close

Set db = Nothing

End Sub
'------ end of "simple" DAO code ------

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Pardon my jumping in so late, but couldn't you create the table and populate
it at the same time with a make-table query?

strSQL = "SELECT """ & strVersion & """ AS Version INTO VersionInfo IN
'C:\Program
Files\InvestigatorNerd\InvestigatorNerd_be.mdb'"

Then do your linking?
(You might also want to define a primary key on the new table)

- Turtle
 
OK Dirk,

I still had the latency problem with this suggestion. I have changed the
way I approach the problem so I just sort of side-step the problem. I'll
keep looking for another solution but in the meantime............

--
Karen
Karen said:
Dirk,

OK, I'll print and really study this. Check back tomorrow and I'll be
sure to post what I hope is the solution for me with this info to work
from. Thanks for all the help.

--
Karen
Dirk Goldgar said:
I dunno; I haven't seen the code you're using for that. If the
back-end database is a Jet database, then probably.
Click to expand...

Doing the same thing with DAO objects would not be significantly simpler
(see below), but using either DAO or ADO you can create the table using
Data-Definition SQL. Compare the two procedures below:

'------ start of "complicated" DAO code ------
Sub CreateVersionInfoTableDAO_Objects(strDBPath As String,
strNewTableName As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim ix As DAO.Index

Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath)
Set tdf = db.CreateTableDef(strNewTableName)

With tdf
Set fld = .CreateField("VersionInfoID", dbLong)
fld.Attributes = fld.Attributes Or dbAutoIncrField
.Fields.Append fld
Set fld = Nothing
.Fields.Append .CreateField("Version", dbText)

Set ix = .CreateIndex("PrimaryKey")
With ix
.Fields.Append .CreateField("VersionInfoID")
.Primary = True
End With

.Indexes.Append ix

End With

db.TableDefs.Append tdf

Set ix = Nothing
Set tdf = Nothing
db.Close

Set db = Nothing

End Sub
'------ end of "complicated" DAO code ------

'------ start of "simple" DAO code ------
Sub CreateVersionInfoTableSQL(strDBPath As String, strNewTableName As
String)

Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath)

strSQL = _
"CREATE TABLE [" & strNewTableName & "] (" & _
"VersionInfoID COUNTER NOT NULL " & _
"CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Version CHAR" & ")"

db.Execute strSQL, dbFailOnError

db.Close

Set db = Nothing

End Sub
'------ end of "simple" DAO code ------

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Back
Top