UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Listbox Wont Populate After SSMA Conversion To SQL Server, Any Versions    
 
   
tuufbiz
post Apr 15 2018, 08:04 AM
Post#1



Posts: 58
Joined: 19-March 18



I have A2003 and SQL server 2016 express. I used SSMA linked option to migrate the tables over to SQL server
My listbox no longer populates.
The SQL statement for the rowsource is:

CODE
strsql = "select tbltravel.confnum as [conf#],tbltravel.priceconf as [conf$],tbltravel.datetravel as[travel date],tbltravel.dateexpire as [expire],tbltravel.citydepart as [dep],tbltravel.cityarrive as [arrv],tbltravel.rndtrip as [rtrip],tbltravel.pricemichael as [mike],tbltravel.pricemichelle as [mich],tbltravel.priceisabella as [isab],tbltravel.pricebianca as [bian],getexpirationdays([qryTravelExpire].[confnum]) as [#dayexp],statusflight as [flight],tblTravel.alert as [alert] from tblTravel INNER JOIN qryTravelExpire ON tblTravel.confnum = qryTravelExpire.confnum where dateexpire >= now and priceconf > 0 and status = 'yes' order by statusflight asc,datetravel asc"



Since A2003 is my front end now --- do I refer to the table in VBA using tblTravel or the new name SQL server made SSMA$tblTravel$local


Thanks for any help
This post has been edited by tuufbiz: Apr 15 2018, 08:04 AM
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 08:35 AM
Post#2


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


" SSMA$tblTravel$local" Really? That is the name of the table in SQL Server? Or is that the name of the local Access table, renamed because of the migration?

Frankly, I've never seen a table name like that generated by SSMA, so I'm sort of leery about how it came into existence, where and when.

Thanks.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 08:36 AM
Post#3


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


",,,status = 'yes'..."

Is status a varchar() field in SQL Server? Or is it supposed to be a byte (i.e. Boolean or Yes/No) field?


--------------------
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 09:05 AM
Post#4



Posts: 58
Joined: 19-March 18



The weird table name was automatic during the SSMA migration (that is what is displayed within access). When I open SQL server the table name is dbo.tblTravel
The status field is varchar
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 09:22 AM
Post#5


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


I think we need a closer look at the icons associated with this table.

I would NOT expect to see a name like that in Access or in SQL Server, but it might be that you choose some setting in SSMA that did it.

Can you upload a screen shot of the navigation pane from your Access accdb? Thanks.

--------------------
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 10:16 AM
Post#6



Posts: 58
Joined: 19-March 18



What is the navigation pane? Is that the access database window that displays the tables, queries, etc...? Also, I'm using A2003 so its an .MDB file on the fe
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 10:33 AM
Post#7



Posts: 58
Joined: 19-March 18



I've uploaded the main database window..... I think this is what you asked for
Attached File(s)
Attached File  Capture2.JPG ( 50.59K )Number of downloads: 0
 
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 11:10 AM
Post#8


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


Thanks. That's pretty much what I thought might be the case.

You can see two kinds of icons. Round green ones and the familiar Access table icons.

What has happened here is that SSMA copied your local Access tables into SQL Server.

Then, it RENAMED those local tables as you now see them.

Then it LINKED your accdb to the new SQL Server tables.

So, to use the new SQL Server linked tables, you need to refer ONLY to those SQL Server tables (the ones identified by the round, green icon) in ALL of your queries. In fact, it's probably safe to delete the old, obsolete, local tables entirely. You should make a backup copy first, as is always a good idea.

Now, with only the new SQL Server tables to work with, things ought to go better. You'll find the syntax issues in your queries more easily once you clear out the ambiguities.

I'm also assuming that the SQL Server tables were created AND populated when you ran the SSMA job.
This post has been edited by GroverParkGeorge: Apr 15 2018, 11:12 AM

--------------------
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 11:23 AM
Post#9



Posts: 58
Joined: 19-March 18



Yes the server tables were transferred fine into the SQL database.
You recommend to refer to only the green icon names.....these names are the same as before using the SSMA. For example tblTravel before is still tblTravel now but just with round green icon. In essence nothing has changed with the name so I.m confused as to why the list box won't populate....only the headers are displayed
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 12:14 PM
Post#10


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA




Those linked tables now reside ONLY in SQL Server. At this point Access has a little link telling it where to go to get the data, but that data is in SQL Server.

So, next step. COnfirm that the tables are populated. We assume they are, but let's confirm it.

DOuble-click on them to open them in datasheet view. See the right data?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 12:18 PM
Post#11


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


Assuming that you have confirmed the data made it safely into SS, let's start looking at the datatypes.

I'm going back to that status field. What is it's data type again? If it was text in Access, it should have been converted to a VarchChar, or NVarChar in SQL Server. If you open the table in Access, in design view, what does Access say about it's datatype? Access will think it's either Long Text, or more likely, Short Text, right?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 12:21 PM
Post#12


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


Okay, moving ahead, if it's a text field, it should be populated with values corresponding to "yes" and, i would imagine, "no". Is that right still?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 12:22 PM
Post#13


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


And if we get this far and all is good, we want to look at that other query, the one joined in this one: qryTravelExpire

Is it good? Is it getting the records you want to see?

--------------------
Go to the top of the page
 
PhilS
post Apr 15 2018, 04:49 PM
Post#14



Posts: 451
Joined: 26-May 15
From: The middle of Germany


Your rowsource was copied verbatim?
Then I make a quick guess:
...where dateexpire >= now() and ..
Add the brackets and it could/should work. - But, actually, this should not have worked without them in a pure Access application either.

--------------------
New Access 2019 feature annouced: Modern Charts
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 05:21 PM
Post#15


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


As I read it, this IS an Access query so, yes, it shouldn't matter what the source tables are.

--------------------
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 06:45 PM
Post#16



Posts: 58
Joined: 19-March 18



Yes the rowsource was copied verbatim. I just double checked it.
I copied my original.mdb into 2 copies: copy1.mdb and copy2.mdb (these aren't the actual names but stated this way for clarity)
copy1.mdb is a local access file where everything works perfectly
copy2.mdb is the file I used the SSMA on to create the SQL server database


-the tables are definitely there and populated with all the data in SQL server
-the qryTravelExpire works perfectly
-adding the parenthesis to now() did not work
-when I copy the strSQL and paste it into a test query --- the same thing happens --- just the headers show up

question: is the SQL on SQL server case sensitive? With VBA I never worry about that
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 07:47 PM
Post#17



Posts: 58
Joined: 19-March 18



Discovery:
If I remove "now" from the criteria then the query works and at least populates ---- so we know that is the culprit.
now() does not work --- so any suggestions on how to deal with this criteria?


Thanks
Go to the top of the page
 
MadPiet
post Apr 15 2018, 08:10 PM
Post#18



Posts: 2,412
Joined: 27-February 09



GETDATE() is the SQL Server equivalent of NOW(). Use that instead.
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 08:12 PM
Post#19



Posts: 58
Joined: 19-March 18



Update:

I actually got it to work by doing the following:

CODE
dim dte as date
dte = now()

strsql = "select tbltravel.confnum as [conf#],tbltravel.priceconf as [conf$],tbltravel.datetravel as [travel date],tbltravel.dateexpire as [expire],tbltravel.citydepart as [dep],tbltravel.cityarrive as [arrv],tbltravel.rndtrip as [rtrip],tbltravel.pricemichael as [mike],tbltravel.pricemichelle as [mich],tbltravel.priceisabella as [isab],tbltravel.pricebianca as [bian],getexpirationdays([qryTravelExpire].[confnum]) as [#dayexp],statusflight as [flight],tblTravel.alert as [alert] from tblTravel INNER JOIN qryTravelExpire ON tblTravel.confnum = qryTravelExpire.confnum where dateexpire >= '" & dte & "' and priceconf > 0 and status = 'yes' order by statusflight asc,datetravel asc"


This was a work-around so if anyone knows how to refer to now() with SQL ---- please let me know


Thanks
Go to the top of the page
 
tuufbiz
post Apr 15 2018, 08:15 PM
Post#20



Posts: 58
Joined: 19-March 18



Thanks Madpiet but I did try that and still it did not work.
I did get the listbox to work but I had to declare a variable and then put it into the strSQL
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:47 AM